subtract datetime in excel

Question

I'm trying to subtract two dates in two separate columns in excel, to find time difference. in h:mm format. The source format is the following.

11/18/2014 9:42:39 AM - 10/8/2014 12:17:13 PM

I have updated the columns to a date with the correct format.

When I subtract the two columns from eachother i get the following hh:mm value.

21:25

It seems to me that the calculation is not taking the dates into account.

What can i do to correct this?

Answer 1

The calculation works correctly, but "hh" does not convert days to hours. Format the result as [h]:mm instead.

Answer 2

Ok, the issue is that you need to convert the dates to number format, and then due the calculations.

Step 1) Convert both dates to numbers, with 4 decimal places

Step 2) Subtract the numbers, to give the time difference between the dates. The numbers before the decimal place are full days, and the numbers after the decimal place are hours, minutes, seconds, etc.

Step 3) Use =Mod(XX11,1)*24 to separate the hours, minutes, seconds from the days, and calculate the number of hours. Repeat for minutes, and seconds, as needed, using 60 instead of 24.

You will now have separate amounts of days, hours, minutes, etc. Put these together in a formula as necessary.