#### Working with dates and time

```I need to calculate the elapsed time in hours from data in 4 date and time
columns (date dd/mm/yyyy format and time in 24 hour format) e.g.

Start Date        Start Time        Stop Date     Stop Time
10/12/2008         15:10            13/12/2008        12:50
11/12/2008         02:00            11/12/2008        16:30
11/12/2008         10:10            13/12/2008        09:00

I need to then be able to find the average elapsed time in hours.

Manually the solution for the above is:

69:40
14:30
46:50
Average elapsed time: 43:40

As I am not used to working with date and time fields, any help would be
much appreciated as I have several hundred lines of data and manually
calculating this would drive me nuts.

~John

```
 0
Inca (2)
12/9/2008 3:48:20 AM
excel.newusers 15348 articles. 2 followers.

5 Replies
425 Views

Similar Articles

[PageSpeed] 8

```=(C1+D1-A1-B1)*24
formatted as decimal will give you hours 1.5 meaning 1 hour and 30 minutes
You can copy this formula down, average and convert to hours and mins...

See http://www.cpearson.com/excel/datetime.htm to understand date and time
calculations in Excel

"Inca" wrote:

> I need to calculate the elapsed time in hours from data in 4 date and time
> columns (date dd/mm/yyyy format and time in 24 hour format) e.g.
>
> Start Date        Start Time        Stop Date     Stop Time
> 10/12/2008         15:10            13/12/2008        12:50
> 11/12/2008         02:00            11/12/2008        16:30
> 11/12/2008         10:10            13/12/2008        09:00
>
> I need to then be able to find the average elapsed time in hours.
>
> Manually the solution for the above is:
>
> 69:40
> 14:30
> 46:50
> Average elapsed time: 43:40
>
> As I am not used to working with date and time fields, any help would be
> much appreciated as I have several hundred lines of data and manually
> calculating this would drive me nuts.
>
> ~John
>
>
>
```
 0
Utf
12/9/2008 4:27:02 AM
```Try this array formula** :

=AVERAGE((C1:C3+D1:D3)-(A1:A3+B1:B3))

Format the cell as [h]:mm

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP

"Inca" <Inca@zzz.com> wrote in message
news:ujdqOFbWJHA.4168@TK2MSFTNGP06.phx.gbl...
>I need to calculate the elapsed time in hours from data in 4 date and time
>columns (date dd/mm/yyyy format and time in 24 hour format) e.g.
>
> Start Date        Start Time        Stop Date     Stop Time
> 10/12/2008         15:10            13/12/2008        12:50
> 11/12/2008         02:00            11/12/2008        16:30
> 11/12/2008         10:10            13/12/2008        09:00
>
> I need to then be able to find the average elapsed time in hours.
>
> Manually the solution for the above is:
>
> 69:40
> 14:30
> 46:50
> Average elapsed time: 43:40
>
> As I am not used to working with date and time fields, any help would be
> much appreciated as I have several hundred lines of data and manually
> calculating this would drive me nuts.
>
> ~John
>

```
 0
biffinpitt (3172)
12/9/2008 4:29:09 AM
```Hi,

Try:

=AVERAGE(24*(C2:C4+D2:D4-A2:A4-B2:B4))    (array entered)

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire

"Inca" wrote:

> I need to calculate the elapsed time in hours from data in 4 date and time
> columns (date dd/mm/yyyy format and time in 24 hour format) e.g.
>
> Start Date        Start Time        Stop Date     Stop Time
> 10/12/2008         15:10            13/12/2008        12:50
> 11/12/2008         02:00            11/12/2008        16:30
> 11/12/2008         10:10            13/12/2008        09:00
>
> I need to then be able to find the average elapsed time in hours.
>
> Manually the solution for the above is:
>
> 69:40
> 14:30
> 46:50
> Average elapsed time: 43:40
>
> As I am not used to working with date and time fields, any help would be
> much appreciated as I have several hundred lines of data and manually
> calculating this would drive me nuts.
>
> ~John
>
>
>
```
 0
12/9/2008 6:26:00 AM
```Thanks to all who gave me feedback.  Much appreciated.  This has solved my
problem and have saved me heaps of time.

~John

```
 0
Inca (2)
12/9/2008 11:04:38 PM
```You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

"Inca" <Inca@zzz.com> wrote in message
news:%2338SIKlWJHA.4632@TK2MSFTNGP04.phx.gbl...
> Thanks to all who gave me feedback.  Much appreciated.  This has solved my
> problem and have saved me heaps of time.
>
> ~John
>

```
 0
biffinpitt (3172)
12/10/2008 2:29:02 AM

Similar Artilces:

Date format issue when submitting from a userform to a spreadsheet
Hi, I have a userform that I've generated which routes dates onto a spreadsheet based on the users input. I am having a bit of a frustrating time with the dates, it would appear that in the process of moving the date from the userform to the spreadsheet some dates are switched/transposed. I'll give an example. If someone enters 09/02/2004 on the userform excel seems to look at 09 and assume it is a month and transposes the dates to 02/09/2004(this does not appear to be a US/UK format issues as I have already gone down that road). However, if the date 13/09/2004 is entered i...

Appointment times won't post correctly
On the calendar, appointments, meetings, etc. made or received show up on the date and time received or sent; regardless of the actual date or time of the appointment. Is there a setting that will correct this? I've exhausted myself trying to find a fix. Outlook 2003 There is a seperate calendar time zone setting that has to be set in addition to your system clock. Pain in the ass. Go to Options and then select Calendar. You should see the Time Zone button there. Set it accordingly, but be aware that everything in your calendar up to that point will change. "Keith Wilso...

office 2003 w/ windows 7 requires reacceptance ot T&C's each time
office 2003 w/ windows 7 requires reacceptance of T&C's each time i open work or excell or any office program. How do I get rid of this? Boot into Safe mode in Windows 7,open any Office app and then accept the terms -- Peter Please Reply to Newsgroup for the benefit of others Requests for assistance by email can not and will not be acknowledged. "mouser830" <mouser830@discussions.microsoft.com> wrote in message news:E7B7602D-F5D8-4559-98EF-A2CCC1E2D6FD@microsoft.com... > office 2003 w/ windows 7 requires reacceptance of T&C's each time i op...

Recipient policy not working for OU
Exch2003 sp2, win2003 sp1 I want users created in one particular OU to get a different primary SMTP address. I've created the Recipient Policy with a filter for the OU and Apply this Policy now, but users created in that OU still get the Default Policy SMTP addressing. Doesn't seem like it could be any easier. I ran Update Now on the RUS. Not sure what else to even check. Why isn't it working? Any suggestions? A recipient policy filter based on the OU won't work. Try using populating the company or department attribute with a value and then using that. Nue "bruce&...

Set default time zone for all users
Is there a way to make the time zone default to a certain value so that I don't have to go into every user and change it to the correct zone? No. Each user must change their own timezone settings. I have no idea why Microsoft do not pick up your computers timezone. Hopefully this will be changed in a version 2.0 release "Jack Black" <anonymous@discussions.microsoft.com> wrote in message news:6F5F4A10-B6AD-420C-9ACB-F73A13FDABB9@microsoft.com... > Is there a way to make the time zone default to a certain value so that I don't have to go into every user and change i...

Clip art works on first try from web then doens't.
Alright I am back. I got something to come up for the web collections clip art now but it seems to only work the first time I do a search right after I open the program, after that I either get a bunch of picture symbols with the little globe in the corner or I get nothing. This clip art thing is really driving me crazy. Anybody know what is up? ridergroov <ridergroov1@comcast.net> was very recently heard to utter: > Alright I am back. I got something to come up for the web collections > clip art now but it seems to only work the first time I do a search > right after I ope...

Keyboard shortcut for current date and time
Hi, Ctrl+ ; inserts current date and Ctrl+Shift+; inserts current ti me Ctrl+Shift+; inserts the current time with the date serial as 0 and not the current date's date serial. Presently I am adding the two (ie current date and time) to get the current date and time. Is there a keyboard shortcut that does this? Thanks in advance. Regards, Raj CTRL+; then SPACE then CTRL+SHIFT+; -or- =Now() -- Jim Cone Portland, Oregon USA http://www.mediafire.com/PrimitiveSoftware "Raj" <rspai9@gmail.com> wrote in message Hi, Ctrl+ ; inserts current dat...

Is SQL 2000 tran working with VS2008?
I code a store procedure mySP in SQL 2000 in which using transaction. I used two ways to test after saving sp. 1) rename one table or 2) rename column name If I use "exec mySP" in SQL 2000 query window, error will catch but ASP.NET try-catch will catch nothing, it always return no error. Is SQL 2000 tran working with VS2008? -- Message posted via DotNetMonster.com http://www.dotnetmonster.com/Uwe/Forums.aspx/asp-net/201002/1 On Feb 5, 10:15=A0pm, "aspfun via DotNetMonster.com" <u53138@uwe> wrote: > I code a store procedure mySP in SQL 2000...

toggling between open work books
Re EXCEL97 under WinXP-Pro w/SR-2 Microsoft Windows allows you to easily toggle between applications using Alt-Tab. Is there a similar key board to toggle between two open workbooks in Excel? One can use Alt-W to display list of open workbooks, but you can�t just blinding hit the up or down arrow, because the most recently accessed workbook is not always at the top or bottom. I realize that I could get around the problem of opening two copies of Excel and then toggling using the Alt-Tab keys, but typically I�ve got two workbooks open in one copy of Excel before I realize I need the f...

SECOND TRY< WHY NO ANSWERS ??? WINXP PRO,OFFICE 2003PRO not working write. WHY ?
> I bought a Windows XP upgrade disk from a friend and it > installs ok, but has this dumb key on it that starts out > with "XXXXX-640-blah-blah-blah" (why does it use X's ?????) Whats the matter with it, > is the installer or the software no good ?????? I paid > good money for this crappe and get this and it cleaned out > all my email and Kazaa. > Next MS mess, how come Outlook 2003 (has almost the same > bozo key number) does not send my messages to the forums > and I have to use this way ? Is it not working too ? Like > do they check a...

Compare Now() to a European date
This is driving me nuts, I have a list of certificates. In column B their expiry dates are entered as Europeans, some at least, do. Like today would be 20080524. I want these cells to change colour with conditional formating. For instance becoming yellow when there is less than three months between now and the expiry date, and then becoming red when there is less than one month to expiry. Else they should remain without colour. I have read through a hundred posts dealing with similar needs and seemingly fine replies, but I get nowhere with my particular sheet. When I format my B cell as ...

Formula for date field
1.I have simple dates in one column (say column A) . 2.In the next column(Column B) I would like the date five months after Column A to be displayed.Eg if Column A has an entry of 9th June 2007,Column B should display 8th November,2007. 3.A simple formula does not do the job as this does not take into account the different number of days in different months! regards S.Sanatani Your post is a bit ambiguous since you don't really say how the different number of days in months should be handled. One way: A1: <date> B1: =DATE(YEAR(A1),MONTH(A1)+5,DAY(A1)-1) In articl...

Date Format turn to Year
Hi, I tried to convert the date to YEAR and then the year plus 25 Years later. =Year(A1) I'm getting the result 1900 instead of 1965. I tried to add 25 years later to 1990 from 1965. Your help would be much apprecated. Thanks What's in A1? Are you sure it's a real date? "learning_codes@hotmail.com" wrote: > > Hi, > > I tried to convert the date to YEAR and then the year plus 25 Years > later. > > =Year(A1) I'm getting the result 1900 instead of 1965. > > I tried to add 25 years later to 1990 from 1965. > > Your ...

DLookup not working
My code is If DLookup("Mechanic", "PartSuffixTbl", "Mechanic = " & CLng(MechanicList.Column(0)) & "") = MechanicList.Value Then MechanicList.SetFocus MechanicBox.Value = "" MessageBox = "This Mechanic cannot be modified because" MessageBox2 = "there are Wheels assigned to it" Me.refresh This is to look in the Mechanic column of PartSuffixTbl and if the MechanicList.Value is found, the following code takes place. I have and else also but the problem is...

New Named Range Created Each Time Data Imported into Excel via Macro
I have noticed that each time I import data into an Excel spreadsheet via a macro, a new named range (for the same range) is created. This does not pose a problem, but after a while, I'll have a huge number of named ranges that will never be used. Why does Excel name the range and how can I stop this? I noticed in the recorded macro, there was a line .Name = "drd_5". I commented this out to see what would happen, but it just renamed the range "ExternalData_5". The next one was "ExternalData_6", etc. Thanks, Carroll Rinehart You could refresh with differen...

why I see times new roman?
I have several html email messages composed in outlook express 6, I'm sure I highlited all text and set it to Arial 10. Then I drag the file to desktop, move it to vista windows mail draggin into inbox or other folder. The result is some parts of text shows arial 10 but some others Times new roman 10 or 12 what is annoying, because I use all arial 10, so I have to manually forward the email, highligh it again and set arial 10 and then, yes, I can see it properly. I checked the compose default font and everything is fine. Why is that? Is there any work around , helo...

using dates Part 2
Karl was great in helping me get to this point with dates, now I'm wondering if we can take it 1 step further? For Activity Dates prior to 2/1/2007 they are using a normal reporting year and the formulas below take care of Activity dates >2/1/2007? So for example prior to 2/1/2007 1/1/2006 would have a B_Qtr of 2006-1 1) B_Qtr - 2011-1 --- Format(DateAdd("m",11,[ActivityDate]), "yyyy - q") 2) Year - 2011 ---- Year(DateAdd("m",11,[ActivityDate])) 3) Qtr - Q1 ---- Format(DateAdd("m",11,[ActivityDate]), "q") -- Than...

Today's date on an Active X Calendar
Could anyone tell me how to set the properties so that the ActiveX calendar I have in the database, displays the current date when the program is openend. I thought this would have been easy, but obviously not! Thanks for any help. CEL504 wrote: > Could anyone tell me how to set the properties so that the ActiveX calendar I > have in the database, displays the current date when the program is openend. > > I thought this would have been easy, but obviously not! > > Thanks for any help. http://groups.google.com/group/comp.databases.ms-access/msg/1564d683ede98f8c Jame...

time #11
I have set up a spreadsheet with time based on what our company truck drivers work. We work under a DOT rule of 70 hours / 8 days. That means that the driver can not work more than 70 hours in an 8 day period. The sheets I have (one for each month) goes back 7 days plus the current day and calculated how many hours a day the driver works and subtracts it from 70, leaving how many hours they can work the next day. If the calculation gets to 0 or above, the cell changes color to alert me that the driver's 70 hours are up and he/she has to have 34 hours off before he/she can be on duty ...

keyboard doesn't work
The keyboard works to type in the password but doesn't work when the desktop appears. I whent thru the hardware trouble shooter. The keys beep when you press them. -- Carol Type of keyboard????? "Carol" <Carol@discussions.microsoft.com> wrote in message news:4120BCCB-DB63-4C37-91D4-B0A7BB1340F7@microsoft.com... > The keyboard works to type in the password but doesn't work when the > desktop > appears. I whent thru the hardware trouble shooter. The keys beep when > you > press them. > -- > Carol On Wed, 10 Mar 2010 10:2...

prompting to save workbook every time (even when no changes made)
i have 3 workbooks all very similar and every single time i open any o them, even when not changing anything, i am prompted to save th thing. its so bad that i can literally open the workbook then click the clos button straight away and it STILL prompts me to save it because i seems to think something has changed. as far as i can see i dont have any macros or anything that will ru every time it starts and change something (i did have a = today in on cell which kept the date as today but taking this out made n difference -- Message posted from http://www.ExcelForum.com Hi Neowok! You prob...