Calculate total time in hours between 2 dates

dpetersman

New Member
Joined
Mar 26, 2009
Messages
5
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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
 
Upvote 0
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"
 
Upvote 0
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 :confused:

Thanks so much for your help!
Dave
 
Upvote 0
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
AB
101/03/2009Date Off Duty
212:01:00 AMTime Off Duty
303/03/2009Date Back On
42:46:00 PMTime Back On
562:45Time Off
Time Off
 
Last edited:
Upvote 0
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
AB
101/03/2009Date Off Duty
212:01:00 AMTime Off Duty
303/03/2009Date Back On
42:46:00 PMTime Back On
562:45Time Off
Time Off

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

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top