Calculate duration (h:mm) between two dates/times

Dear Sir/Mdm,

I'm trying to calculate hours and minutes (h:mm) between two dates/times over more than 24hrs.


I have for example:-


Start Date/Time within the same cell - A2 is 21/07/10 08:47:00
End Date & Time within the same cell - B2 is 23/07/10 05:12:00

Attached a sample of my file.
What formula can I use?

Originally Posted by daddylonglegs

You can use a simple subtraction, e.g. in C2 copied down

=A2-B2

Custom format C2 as [h]:mm

I believe you meant =B2-A2? The results I received are #VALUE! There are no numbers (h:mm) displayed.

You need to first convert the data in Cols A and B and then do the math. I have attached what I think is a simple fix without code.

Originally Posted by daddylonglegs

Sorry, yes, I meant B2-A2

That works for me, my regional settings recognise dd/mm/yyyy dates rather than mm/dd/yyyy. What is your default date format? If it isn't dd/mm/yyyy then optomyst's suggestion should work for you......I think you can use a shorter version though, try

=REPLACE(MID(B2,4,14),4,0,LEFT(B2,3))-REPLACE(MID(A2,4,14),4,0,LEFT(A2,3))

format C2 as [h]:mm

Great! It works. Thank you so much.
Is it a problem calculating duration if the file I received has dates in the mm/dd/yy format?

By the way, how do I sort the column of data from earliest to latest with such date/time format (21/07/10 08:47:00)?