Subtracting two date-times excel.?
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.
Garbo's formula got me somewhere...
Unfortunately, I have two cells: I1 1/1/2020 00:18:29 and J2 01/01/2020 00:57:03, which should only be like a 40-minute difference, but Garbo's formulas are returning 24:38:34.
Others are working, though.
Another thing that's baffling me is that I have 4 columns with date-times, and three of them are formatted correctly, but the fourth refuses to get rid of AM/PM even though I'm using a 24-hour format.
I've cleared formatting a dozen times.
- garbo7441Lv 71 month agoFavorite Answer
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.
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:
*Yes, this is a kludgy solution. I usually prefer to handle things like this using VBA. Just seems easier to me.
- expletive_xomLv 71 month ago
Hi Garbo, hope you are doing well. i'm not a big fan of Yahoo/Verizon actively trying to drive people away.
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
- David KLv 61 month ago
Enter 'subtract dates in excel' into your favorite search engine and you will see many websites with detailed instructions.