# Calculating total hours between two dates

#### SNA400

##### New Member
I need to count the number of hours between 2 dates. example I need the difference between 21/04/2014 00:10 and 23/04/2014 23:50 (answer is 47h 40 mins)

The cells have a start/finish date/time in the format dd/mm/yyyy hh:mm

When I minus the start date/time from the finish date/time I want the result to be number of total hours.

I have tried SUM(A1-B1) but this only calculates the difference between the two hour totals

I have also tried the formula below
=NETWORKDAYS(D4,E4)+NETWORKDAYS(E4,E4)*(MOD(D4,1)-1)-NETWORKDAYS(D4,D4)*MOD(D4,1)
but this only shows me the time entry in D4

I an sure there is a simple answer to this question - but I can't work it out!

Simon

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

=(b1-a1)*24

#### Momentman

##### Well-known Member
Steve's formula should work? But from your example, the difference between both times isn't 47hours, its about 72hours except am not doing something correctly

#### VoG

##### Legend
I make it 71:40 using

=B1-A1

and formatting as Custom [h]:mm

#### Momentman

##### Well-known Member
I make it 71:40 using

=B1-A1

and formatting as Custom [h]:mm

@VOG:True sir

or =TEXT(A2-A1,"[h]:mm")

#### SNA400

##### New Member
Sorry guys

Dont think I made myself clear - I need to calculate the difference in total hours between when the container is booked for and when it was tipped and also the difference from arrival to when tipped

A portion of the sheet I have is below - the third container in the list on the arrival to tip is using =SUM(E4-D4)*24 - and the calculation should be around 10 hours - not 22.
the container calculation for arrival to tip on the first row should be around 53 hours - not the 4:57 shown

So you can see my dilemma! all the dates and times are all over the place so I need a simple way of getting the difference.

 Container No. Container Type Booked Delivery Date Container Arrived Container Tipped Container Departed DIF BOOKED TO TIP DIF ARRIVAL TO TIP TCNU5303474 40ft HC 23/06/2014 04:00 21/06/2014 06:21 23/06/2014 11:18 23/06/2014 20:06 06:21 0.264583 1 04:57 2.20625 1 HJCU1077530 40ft HC 23/06/2014 04:00 21/06/2014 00:49 23/06/2014 13:12 24/06/2014 00:52 07:18 0.304167 1 12:23 0.515972 1 HJCU1523333 40ft HC 23/06/2014 04:00 22/06/2014 19:41 23/06/2014 05:37 23/06/2014 11:38 14:48 1.616667 1 22:24 9.933333 1 GVDU5031900 40ft HC 23/06/2014 04:00 22/06/2014 19:45 23/06/2014 10:52 23/06/2014 14:03 06:52 0.286111 1 15:07 0.629861 1

<tbody>
</tbody>

#### steve the fish

##### Well-known Member
Dates and times are just numbers to excel. The date is the whole number part and the time is the fraction part. Therefore 9.933 timewise is just 0.933 of a day. Multiply 0.933 * 24 and you see where the 22.24 is coming from. They are both the same number.

#### steve the fish

##### Well-known Member
If you want the formula to say the time difference in words:

=INT((B1-A1)*24)&" hours "&ROUND(((B1-A1)*24-INT((B1-A1)*24))*60,0)&" minutes"

#### SNA400

##### New Member
Thanks Steve - got it working whan I re read an earlier post and corrected the error!

Now all I have to do is work out how to display the negative times (where the container was tipped before its booked time....

Replies
6
Views
949
Replies
4
Views
2K
Replies
3
Views
2K
Replies
1
Views
593
Replies
3
Views
508

1,196,007
Messages
6,012,829
Members
441,732
Latest member
Ayon

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