Anonymous
Anonymous asked in Computers & InternetSoftware · 1 month ago

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.

Update:

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.

3 Answers

Relevance
  • 1 month ago
    Favorite 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. 

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

  • 1 month ago

    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)

  • 1 month ago

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

Still have questions? Get your answers by asking now.