Add time to a date time group

Hi, thanks for any help in advance

I have my date and time displayed in this format  dd hhmm "K" mmm yy

which shows up as 03 1800 K Sep 08.

I would like a formula that will add 30 min to this time group so it becomes
 03 1830 K Sep 08


0
Patrick2861 (490)
9/3/2008 11:28:00 PM
excel 39879 articles. 2 followers. Follow

4 Replies
639 Views

Similar Articles

[PageSpeed] 10

Assume your date/time is in A1, then you can do this in another cell:

=3DA1+30/24/60

Format the cell in the same way as A1 (using the Format Painter icon).

Times are stored in Excel as fractions of a 24-hour day, hence the
divisions by 24 and 60.

Hope this helps.

Pete

On Sep 4, 12:28=A0am, Patrick <Patr...@discussions.microsoft.com> wrote:
> Hi, thanks for any help in advance
>
> I have my date and time displayed in this format =A0dd hhmm "K" mmm yy
>
> which shows up as 03 1800 K Sep 08.
>
> I would like a formula that will add 30 min to this time group so it beco=
mes
> =A003 1830 K Sep 08

0
pashurst (2576)
9/3/2008 11:41:27 PM
another one:

=a1+time(0,30,0)


Patrick wrote:
> 
> Hi, thanks for any help in advance
> 
> I have my date and time displayed in this format  dd hhmm "K" mmm yy
> 
> which shows up as 03 1800 K Sep 08.
> 
> I would like a formula that will add 30 min to this time group so it becomes
>  03 1830 K Sep 08

-- 

Dave Peterson
0
petersod (12005)
9/4/2008 12:08:33 AM
Thankyou that helped, I am still haveing some problems in this worksheet 
though, the next problem is I need to calculate the time differance between 2 
date time groups and add it to another, for example

I have a tme line, lets say I have a meeting that is going over time, once I 
have entered the change in time I need it fint the time differance in cell A1 
and B2 and add it to cell A3
        A1                              B1                        
03 0730 K Sep 08	03 1000 K Sep 08    (morning meeting)

       A3
03 0830 k Sep 08   (Breakfast)


"Pete_UK" wrote:

> Assume your date/time is in A1, then you can do this in another cell:
> 
> =A1+30/24/60
> 
> Format the cell in the same way as A1 (using the Format Painter icon).
> 
> Times are stored in Excel as fractions of a 24-hour day, hence the
> divisions by 24 and 60.
> 
> Hope this helps.
> 
> Pete
> 
> On Sep 4, 12:28 am, Patrick <Patr...@discussions.microsoft.com> wrote:
> > Hi, thanks for any help in advance
> >
> > I have my date and time displayed in this format  dd hhmm "K" mmm yy
> >
> > which shows up as 03 1800 K Sep 08.
> >
> > I would like a formula that will add 30 min to this time group so it becomes
> >  03 1830 K Sep 08
> 
> 
0
Patrick2861 (490)
9/4/2008 12:17:00 AM
I'm not sure what you want here, or what your example is trying to
show. It looks like you have a meeting from 7:30 am to 10:00am, and
you have breakfast at 8:30 am. Are you trying to subtract the length
of time your breakfast took away from the total duration of the
meeting to get the real duration of the meeting? If so, where do you
record the duration of your breakfast?

Or, are you saying that because the meeting runs from 7:30 to 10:00am,
your breakfast has to be rescheduled from 8:30 to the time your
meeting finishes?

And where does B2 come into it?

Pete

On Sep 4, 1:17=A0am, Patrick <Patr...@discussions.microsoft.com> wrote:
> Thankyou that helped, I am still haveing some problems in this worksheet
> though, the next problem is I need to calculate the time differance betwe=
en 2
> date time groups and add it to another, for example
>
> I have a tme line, lets say I have a meeting that is going over time, onc=
e I
> have entered the change in time I need it fint the time differance in cel=
l A1
> and B2 and add it to cell A3
> =A0 =A0 =A0 =A0 A1 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0B1 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
> 03 0730 K Sep 08 =A0 =A0 =A0 =A003 1000 K Sep 08 =A0 =A0(morning meeting)
>
> =A0 =A0 =A0 =A0A3
> 03 0830 k Sep 08 =A0 (Breakfast)
>
>
>
> "Pete_UK" wrote:
> > Assume your date/time is in A1, then you can do this in another cell:
>
> > =3DA1+30/24/60
>
> > Format the cell in the same way as A1 (using the Format Painter icon).
>
> > Times are stored in Excel as fractions of a 24-hour day, hence the
> > divisions by 24 and 60.
>
> > Hope this helps.
>
> > Pete
>
> > On Sep 4, 12:28 am, Patrick <Patr...@discussions.microsoft.com> wrote:
> > > Hi, thanks for any help in advance
>
> > > I have my date and time displayed in this format =A0dd hhmm "K" mmm y=
y
>
> > > which shows up as 03 1800 K Sep 08.
>
> > > I would like a formula that will add 30 min to this time group so it =
becomes
> > > =A003 1830 K Sep 08- Hide quoted text -
>
> - Show quoted text -

0
pashurst (2576)
9/4/2008 12:45:26 AM
Reply:

Similar Artilces:

Want to Add more Sub description Field
How I can add more sub description field like 1,2,3,4,5 regards Saleem Suri Salaam Saleem, You have only limit up to 3 fields in RMS 1,2,3 sub-description also you can use the extended description and there is main description for the item. so Altogether you have upto 5 description limit and I think that is enough. Let me know if this won't fulfill your requirements else use the Item Notes for more description. Rate if like Regards Akber "Saleem Suri" wrote: > How I can add more sub description field like 1,2,3,4,5 > > regards > Saleem Suri Salam Akbar ...

Add Actual End Date to Resolved Cases view
Hi We would like to add the actual end date to the Case General Tab or possibly to the Resolved Cases and/or My Resolved Cases view. I know that the Actual End Date is available on the Service Activity and Case Resolution but this field is not available for the Case. Any workaround for this? Thanks Mark ...

How do I add column A data labels to a scatter of columns B and C?
I have made a scatter plot using columns B and C as my x and y coordinates. I would like to use the data in column A to label my points. I already have the XY chart labeler 97 installed and selected, but I don't know how to get the data labels to show up on my graph. Thank you! Hi Christina - There are a couple of well written Excel add-ins you can download to do this: Rob Bovey's Chart Labeler, http://appspro.com John Walkenbach's Chart Tools, http://j-walk.com Both are free downloads, they install easily, and they integrate into Excel's interface very nicely. They l...

Usig sdk to add a note to an existing account
Hi, Anyone know how to add a note programatically to an existing Account? Thanks John Hi John, It really depends on your situation the most obvious way is to use the CRM web services and simply use the create method. Public Sub CreateAnnotation() Dim service As New CrmService() service.Credentials = System.Net.CredentialCache.DefaultCredentials Dim myNote As New annotation service.Create(myNote) End Sub Of course you will need to specify all the required fields; the above alone would fail! including the associated object ID (the GUID of the accoun...

Problem with xy scatter with dates
I am trying to create an x-y scatter chart where both the x values and the y values are dates. I highlight the 2 adjacent columns of date data and click on Insert>Scatter. Instead of a scatter graph, I get a line chart with 2 lines (the two data columns). The x values on the graph are simply a count of the data points as in a line chart. If the 2 columns of data are not dates, I get the correct scatter chart, but not if the x-values are dates in which case I get a line chart. How can I get a scatter chart with both x-values and y-values as dates? You could select just the Y val...

Make Table Query
I have a Macro and at the end of the Macro I have a Make Table Query that I would like to automatically save as todays date in the current database. Does anybody have an idea on how I can do this? On Wed, 06 Jun 2007 11:40:24 -0700, dswiders@gmail.com wrote: >I have a Macro and at the end of the Macro I have a Make Table Query >that I would like to automatically save as todays date in the current >database. Does anybody have an idea on how I can do this? A date is just a field value. Saving today's date as a Table is almost certainly VERY bad design. Why do an expensive, awk...

Can we use group policy templates for Outlook client options
Hi there, We are looking to automate the CRM outlook client settings so we don't have to manually change them for each user. Is there any way we can do this through group policy or another way? It would be handy if we had a list of registry settings that we could use? Thanks ...

Date Picker
Hello, Is there a DatePicker in Excel 2007 so I can pick a date when I focus on a cell? If not, where can I get one? Thanks, Miguel Look here: http://www.rondebruin.nl/calendar.htm -- Kind regards, Niek Otten Microsoft MVP - Excel "shapper" <mdmoura@gmail.com> wrote in message news:776296c8-83d8-467e-aefb-c31b24446bf7@b15g2000yqd.googlegroups.com... > Hello, > > Is there a DatePicker in Excel 2007 so I can pick a date when I focus > on a cell? > > If not, where can I get one? > > Thanks, > Miguel ...

Conditional formatting with dates formula problem.
Hello. I appreciate help on this topic. I'm very new to excel's conditional formatting capabilities and I need help on the following: I have a worksheet where I am using columns A and B to be fashioned into a type of "reverse" library checkout card; I want to flag when 120 days have passed since an item has been checked out. All cells are blank with the exception of the formatting applied to cells in column A. For example, Condition 1 on cell A1 has the formula: =IF(ISBLANK(B1),(A1-TODAY())<120) **I've also tried =IF(ISBLANK($B1),($A1-TODAY())<120) My objecti...

Dates are not displayed in ComboBox
I have a From ComboBox and a To ComboBox on a Form; cboFrom and cboTo. The Row Source is this: SELECT DISTINCT SharePrices.DateTime FROM SharePrices ORDER BY SharePrices.DateTime; The SQL is this: SELECT DISTINCT SharePrices.DateTime FROM SharePrices ORDER BY SharePrices.DateTime; Nothing is showing in either ComboBox; all I see is a whole bunch of blanks. I just did this in another form in another Access database. I can’t figure out why it’s not working for me now. I’m using Access 2007. I’d sincerely appreciate any insight with this. Thanks! Ryan -- Ryan--...

Year to date sales history of inventory
How can this system provide me with cumulative sales numbers of a particular item ie: How many total gym shorts did we sell in 12 months? Detailed Sales Report with a custom date range. In the body of the report look at the top line (Overall) that will give you the answer you seek my child. Now go caterpillar blossom into a butterfly. Rob "KatieH" wrote: > How can this system provide me with cumulative sales numbers of a particular > item ie: How many total gym shorts did we sell in 12 months? Musta got a new batch o glue, huh? (g) KatieH To pick up where Rob started...

email groups
I have tried to send group emails of approx 40 names from my outlook address book......I got bounce backs from 12 the first time I sent and 12 additional names the second time...they are from different domains Technical details of permanent failure: PERM_FAILURE: Probe failed: Illegal To: address (invalid domain name): 'rita311@optonline.net' this is the message I get....can anyone help ? Regards Angelo39 ...

Problkems with timing on meeting requests...
This is kind of bizarre and hopefully easy to fix... We are using the Outlook 2002 client with Exchange 2000. One of our users who has been sending meeting requests to other users. has just informed me that when the requested user gets the meeting request, the requested times are exactly an hour LATER than what she had set the meeting for. (the time on her machine is perfect) Also, when someone sends her a meeting request (vice-versa), the times show up on her Outlook client an hour EARLIER than they should be. What can I look for to correct this issue? It's seems fine for everyone e...

how to enable a control at run time
I have a label that I disable at design time, but I would like to enable it at run time and I can't figure out to do this. I tried using showwindow, but all that does is hide the control. Z.K. Use EnableWindow: ASSERT_VALID(pMyControl); pMyControl->EnableWindow(); More here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vclib/html/_mfc_cwnd.3a3a.enablewindow.asp --------- Ajay Kalra ajaykalra@yahoo.com In addition to what Ajay answered, make sure that you hook into the control with a member variable. Then you can reference all of the members functionalit...

Excel 2003 - VBA
Does anyone have and idea how to create the date of the last day of the previous month? Craig Hi Craig =Dateserial(year(date), month(date), 0) HTH. Best wishes Harald "Craig Brandt" <brandtcraig@att.net> wrote in message news:yvVxk.22131$89.8111@nlpi069.nbdc.sbc.com... > Does anyone have and idea how to create the date of the last day of the > previous month? > > Craig > > =TODAY()-DAY(TODAY()) -- __________________________________ HTH Bob "Craig Brandt" <brandtcraig@att.net> wrote in message news:yvVxk.22131$89.8111@nlpi069.nb...

Listbox not populating every time
I have a form that has two listboxes. Both listboxes are on frmAccount. LISTBOX 1: Show investments inside an account This listbox shows data from tblPositions (a linked csv txt file) In the SQL of the listbox, I set the criteria of AccountNumber field of the table to equal Forms!frmAccount!AccNumber. LISTBOX 2: Show transactions of an account This listbox shows account transactions from tblTransactions (a linked csv txt file) In the SQL of the listbox, I set the criteria of AccountNumber field of the table to equal Forms!frmAccount!AccNumber. The idea of my SQL was to limit so that it ...

Run Time error When Outlook XP starts in WXP
I just upgraded my Dell PII 400 from W2000 SP4 to WXP SP1. I was having a lot of problems with Outlook XP (all updates applied) crashing multiple times while in use, which was 1 reason for the upgrade. Now when I start Outlook, most times I get a message box entitled: 'Microsoft Visual C++ Runtime Library' with the text: 'Runtime Error! Program: C:\PROGRA~1\MICROS~2\Office10\OUTLOOK.EXE abnormal program termination.' When I click OK, Outlook closes. The next time, it starts OK, but has to scan the Personal folder for errors. Fixes? Britt Dickson -- "In an era o...

Append Query every day at certain time automatically
Hi, So i have a query qryStats. Basicaly i want to be able to see how the stats have changed over a period so i was thinking of turning that simple query into an Append Query so that it just add the results to a table tblStats. I tested it and it works great so i can design reports and the result is exactly what i want. Problem is im going to forget to run the query each day. What are my options? Can i write code of some kind that runs the append query each day at, lets say 16:00? YES but your db would need to be running in order for that to work. You could also create...

Office v.x Test Drive Timed Out After Two Weeks
I bought my wife a Macintosh G4 tower. I purchased Office v.x at the same time, but UPS delivered the Office package separately. We found the test drive software on the hard drive and used it while waiting for the Office software. I was waiting for a friend to come back from vacation to get help exporting e-mail from Entourage so I could uninstall the test drive and install the real software. Something happened--my guess is it was configuring the system to use local time (it defaulted to Pacific)--that caused the test drive to suddenly stop working. Is there some way to get the ...

Access autonumber field changes to date format when Excel imports
When importing Access data into Excel, the autonumber field in Access is chenged to date format instead of a general number format. I could run a macro to change the number format but this only happens on some Vista machines, not all. Any suggestions as to why this is happening? ...

Group
I want to group my business e-mail addresses, keeping them seperate from my everyday contacts. I can't find in help where I can do this grouping. Sorry to bother you on such a trivial question. -- Thank-you Carl R Check 'Categories' in help "Carl R" <carlritcey@bendbroaband.com> wrote in message news:7E27F9E8-81B4-42B6-B30C-9970FA0F796C@microsoft.com... > I want to group my business e-mail addresses, keeping them seperate from my > everyday contacts. I can't find in help where I can do this grouping. Sorry > to bother you on such a trivial questio...

What date was a batch created
I'm in GP v8.0. I need to know what date a user created and/or saved a batch. I've looked all over and there doesn't seem to be any trail when a batch was created/saved short of turning on the User Tracking system. Please get back to me ASAP - sdanna@sfgmortgage.com First, as you probably know, this is not a forum for getting instant answers because it is not monitored officially. Second, SY00500 stores the batch history. It is in the company database. You will need to create a report based on this table. -- Charles Allen, MVP "Sandy D''Anna, GP Project Ma...

conditional format a date in Excel
I am trying to use conditional formatting to show when a date is less than 30 days in the future. When I use "cell value" "is less than" "="Now()+30" all the dates change. I am trying to use conditional formatting to show when a date is les than 30 days in the future. When I use "cell value" "is less than" "="Now()+30 all the dates change. lose the quotes in the condition =Now()+30 Unless you mean between today and 30 days in the future in which cas =now() would be the first condition and =now()+30 would be the second Al...

Excel 2002 Day/Date format
For some reason on one of my computers, Excel won't allow the cell to be formatted to read "Day, Month Date, Year". The option doesn't exist under the Date category of the Format Cells command. Anyone have any ideas? Hi Have you tried using a Custom format? -- Andy. "The Kingfish" <KCKingfish@earthlink.net> wrote in message news:MGjke.4830$X92.1081@newsread2.news.pas.earthlink.net... > For some reason on one of my computers, Excel won't allow the cell to be > formatted to read "Day, Month Date, Year". > > The option doesn&...

Install dates formating using conditional formating?
I have 2 cells (A1 and A2). I would like to have the following take place. 1. If I place a date that is either past or withinthe next 30 days into A2 format the text as red. 2. If I place any date into cell A1, I need A2 to say N/A. I have been able to do #1 using conditional formating but am stuck on #2. Can anyone help? Thanks -- Jerry Eggleston ------------------------------------------------------------------------ Jerry Eggleston's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=9898 View this thread: http://www.excelforum.com/showthread.php...