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

Working with dates and time
 

=(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

Working with dates and time
 

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" wrote in message
...
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

Working with dates and time
 

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

Working with dates and time
 

Thanks to all who gave me feedback. Much appreciated. This has solved my
problem and have saved me heaps of time.

~John

Working with dates and time
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Inca" wrote in message
...
Thanks to all who gave me feedback. Much appreciated. This has solved my
problem and have saved me heaps of time.

~John