# Calculate total time in hours between 2 dates

#### dpetersman

##### New Member
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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### dave3009

##### Well-known Member
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

##### Board Regular
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

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

#### dpetersman

##### New Member
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

##### MrExcel MVP, Moderator
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:

#### dpetersman

##### New Member
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!

Replies
3
Views
352
Replies
4
Views
371
Replies
2
Views
358
Replies
21
Views
605
Replies
4
Views
352

1,191,177
Messages
5,985,140
Members
439,942
Latest member
bkexcel11230

### 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.

### Which adblocker are you using?

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

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