Subtracting two date-times excel.?

Question

I am trying to figure out a way to subtract two date-time cells in excel. 

I want to subtract the two cells containing the date and time (dd/mm/yy hh:mm:ss).

Initially, they were both just time cells, but I was getting an error if the end time was greater than the start time (e.g., 01:31:40 - 01:56:27 = #VALUE!) I assume that's because excel didn't know that my "greater" end time was actually 01:56:27 the following day.

So I tried including the date as well, hoping it would be able to calculate the difference, but I still get "#VALUE!". 

I want to show the amount of time in hours, minutes, and seconds between the two cells. 

I do not want to split the cells. 

I need them in this specific date-time format for the project I am working on.

Answer 1

You can use Time values and subtract them using the following example formula. The answer will be returned in Minutes. Put this formula in a helper cell, say C1. 



=IF(B1>A1,((B1-A1)*1440),(24-(A1-B1)*24)*60)



To convert the minutes to your desired format, 'hh:mm:ss', you can use this second formula, assuming the result of the first formula is in C1.



=INT(C1/60)&" hrs "&INT(C1-(INT(C1/60))*60)&" mins "&ROUND(60*(C1-(INT(C1/60)*60+INT(C1-(INT(C1/60))*60))),2)&" seconds"



Or, if you want to show the result in the format 'hh:mm:ss', use this formula:



=INT(C1/60)&":"&INT(C1-(INT(C1/60))*60)&":"&ROUND(60*(C1-(INT(C1/60)*60+INT(C1-(INT(C1/60))*60))),2)



*Yes, this is a kludgy solution.  I usually prefer to handle things like this using VBA. Just seems easier to me.

Answer 2

Hi Garbo, hope you are doing well.  i'm not a big fan of Yahoo/Verizon actively trying to drive people away.



Hi Anonymous.

if the problem is just crossing midnight and =A1-B1 is giving you an error....

then you want to just add 1 with a simple true/false (your "greater")....try this



=A1-B1+(B1>A1)

Answer 3

Enter  'subtract dates in excel' into your favorite search engine and you will see many websites with detailed instructions.