Calculate total time in hours between 2 dates

[dpetersman]

Hi,

I'm trying to calculate the total amount of hours between 2 dates and times in hours.

For example:

a1= date off duty
a2= time off duty
a3= date back on duty
a4= time back on duty
a5= number of hours and minutes between the date & time off duty and the date and time back on duty.

The problem im running into is when the duration spans more then 24 hours.

Any suggestions would be appreciated.

Dave

 

[dave3009]

Hi

Welcome to the Board

I tried

=((D3+D4)-(D1+D2))*24

It assumes that both date and time are formated correctly

All the best


Dave

 

[Joseph.Marro]

here you go try this:

=INT(((A3+A4)-(A1+A2))*24)&" Hours "&INT(RIGHT(((A3+A4)-(A1+A2))*24,LEN(((A3+A4)-(A1+A2))*24)-LEN(INT(((A3+A4)-(A1+A2))*24)))*60)&" Minutes"

 

[Joseph.Marro]

Disregard my last post, I over-simplified the Minutes portion. I can fix it once I get home.

 

[dpetersman]

Hi and Thank you for your replies!

Just to clarify, I don't need the answer to actually read "hours" or "minutes" text. just the numbers.

for example,
a1= 3/1/09 (date 0ff)
a2= 12:01 AM (time off)
a3= 3/3/09 (date on)
a4= 2:46 PM (time on)
a5= 62:45 (total time on duty)

Like I mentioned, i can calculate it fine as long as the duration does not exceed 24 hours. Also, come to think of it, the 30th/31st of one month to the 1st of another month may be a problem as well

Thanks so much for your help!
Dave

 

[Peter_SSs]

An alternative is to use this formula, and format A5 with the Custom format of [hh]:mm (or any other format you want)

Excel Workbook
	A	B
1	01/03/2009	Date Off Duty
2	12:01:00 AM	Time Off Duty
3	03/03/2009	Date Back On
4	2:46:00 PM	Time Back On
5	62:45	Time Off
Time Off

 

[dpetersman]

An alternative is to use this formula, and format A5 with the Custom format of [hh]:mm (or any other format you want)

Excel Workbook
	A	B
1	01/03/2009	Date Off Duty
2	12:01:00 AM	Time Off Duty
3	03/03/2009	Date Back On
4	2:46:00 PM	Time Back On
5	62:45	Time Off
Time Off

Short & Sweet! Worked like a charm. Thanks to all you guys for your help!