Calculating total hours between two dates

[SNA400]

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

 

[steve the fish]

=(b1-a1)*24

 

[Momentman]

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]

I make it 71:40 using

=B1-A1

and formatting as Custom [h]:mm

 

[Momentman]

ADVERTISEMENT

I make it 71:40 using

=B1-A1

and formatting as Custom [h]:mm

@VOG:True sir

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

 

[SNA400]

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]

ADVERTISEMENT

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]

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]

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

 

[VoG]

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

You can only do that if you use the 1904 date system XL: The 1900 Date System vs. the 1904 Date System