0

I am trying to subtract a date/time in cell Format Custom: mm/dd/yyyy hh:mm:ss AM/PM. from another time in the same format. I have tried ideas from so many websites as to how to do it but none show an example of how I wish it done. have tried using DATEDIF formulas, formulas with TIMEVALUE with MINUTE(TIME(HOUR and many more and nothing works .... so Let's say A1 = 11-25-2020 3:30:00 PM A2 = 09-16-2020 7:30:00 AM and A3 = SUM(A1-A2) = 00/03/10 08:00:00 A3 is formatted yy/mm/dd hh:mm:ss

A1 when converted to general = 44160.64583 A2 when converted to general = 44090.3125 A3 when converted to general = 70.33333333 So the date/time to number conversion looks right and the math looks right The issue is with the conversion of number to Date/Time in A3.

Does anyone know any solution to this problem?

Thank you in advance Curt

Improve this question
4
  • 3
    What are your expecting the answer to look like? The date for time code 70.3 is indeed 10 March 1900. That is 70 days since 1 Jan 1900. In my mind, once you subtract two dates from each other, the result is purely a number in days. Formatting it as a date does not help much. What you could do is display it as eg. "10 W 1 D 12 H" (=71.5)
    – ExcelEverything
    Nov 25, 2020 at 23:14
  • Could you provide a simple sample about this problem?
    – Lee
    Nov 26, 2020 at 9:59
  • I'm looking for the answer to be the difference between the 2 Date/Times I see when it posted it did not come out looking like I expected. but I want the answer to be yy/mm/dd hh:mm:ss 00/02/09 08:00:00 but it yeilds an answer of 00/03/10 08:00:00
    – Curt
    Nov 27, 2020 at 2:11
  • Hi there, what I want to do ( but not for the purpose used in the example) is for instance I know the year, month, day, hour, minute and second My daughter was born and I want excel to subtract it from the current year, month, day, hour, minute and second and give me the difference in year, month, day, hour, minute and second. Is there no way to accomplish this?
    – Curt
    Nov 27, 2020 at 2:25

3 Answers 3

Reset to default
0

The problem, of course, is that neither years nor months have a fixed number of days, so the differences need to be calculated individually.

To output the values as a string, you can try:

=TEXT(DATEDIF(A2,A1,"y"),"[<>1]0 ""yrs,""; 0 ""yr,""")&
  TEXT(DATEDIF(A2,A1,"ym"),"[<>1] 0 ""months,""; 0 ""month,""") &
  TEXT(DATEDIF(A2,A1,"md")-(MOD(A2,1)>MOD(A1,1)),"[<>1] 0 ""days,"";0 ""day, """) &
  TEXT(HOUR(MOD(A1,1)-MOD(A2,1)+(MOD(A2,1)>MOD(A1,1))),"[<>1] 0 ""hrs,""; 0 ""hr,""") &
  TEXT(MINUTE(MOD(A1,1)-MOD(A2,1)+(MOD(A2,1)>MOD(A1,1))),"[<>1] 0 ""mins,""; 0 ""min""") &
  TEXT(SECOND(MOD(A1,1)-MOD(A2,1)+(MOD(A2,1)>MOD(A1,1))),"[<>1] 0 ""secs""; 0 ""sec""")

Note that we use the Boolean (MOD(A2,1)>MOD(A1,1)) to determine whether to add/subtract a day from the day and time values to account for an ending time being before a starting time.

enter image description here

If you want a different output format, that is doable. But I would stick with output as *text, because certain differences cannot be expressed with the date format; for example 01-02-29 (1 yr, 2 months, 29 days) since there were not 29 days in February that year).

Improve this answer
4
  • That is perfect, thank you so much Ron, Unfortunately advanced Excel isn't my forte. I really appreciate this - Curt
    – Curt
    Dec 1, 2020 at 4:00
  • Hmmmm sorry to br a PITA but not quite perfect, as I was going to sleep last night I had a thought and just got to testing it now and as I feared in certain conditions it returns the wrong data, for instance if we change A1 to 01/01/2022 6:00:00 AM and A2 to 01/01/2020 12:00:00 PM the results are off, do you think this can be fixed? I cannot believe how difficult something that I thought would be simple is.
    – Curt
    Dec 1, 2020 at 18:25
  • @Curt It's not "wrong". It's just not in a desirable format. After all 0 months, **-**1 days, 18hrs is the same as 11 months,30 days, 18 hrs. If you need not only accuracy, but also the format, I'd suggest you develop a VBA solution. It'll be simpler in the long run. The problem has to do with how the DATEDIF function has limitations.
    – Ron Rosenfeld
    Dec 1, 2020 at 22:42
  • ok thanks Ron thats what I was starting to think as well, I thank you very much for your help and expertise. TakeCcare and Be Well - Curt
    – Curt
    Dec 2, 2020 at 0:54
0

It is hard to see your purpose in the final format so guesses aren't too likely to be correct. Nonetheless, I'll make my best guess and think your purpose is to show the difference in time as a portion of the current year, as a date in the year to give a feel of a different sort than a percentage of the year would do. So I am guessing your desired outcome in the example would be "20/03/10 08:00:00"...

You could accomplish that by adding 43830 to the subtraction in A3. So the elapsed time would be relative to the start of the year. You could future-proof that by using DATEVALUE() and passing it a constructed string that finds the current year and joins "1/1/" to it.

(If that isn't the idea, you need to edit to "clarify" (i.e.: make a mention of SOME kind just what you are looking for). A few more hours and no one will answer it even after an edit and it might get voted Closed too.)

Improve this answer
1
  • Hi there, what I want to do ( but not for the purpose used in the example) is for instance I know the year, month, day, hour, minute and second My daughter was born and I want excel to subtract it from the current year, month, day, hour, minute and second and give me the difference in year, month, day, hour, minute and second. Is there no way to accomplish this?
    – Curt
    Nov 27, 2020 at 2:25
-1

Since you are using Date Timestamp (Date & Time together) then you have to work with different methods. You can't us the DATEVALUE function because it converts a date represented as text into a proper Excel date.

enter image description here

  • If you want to get difference in Years, Months & Days then use this formula in cell O5:

    =DATEDIF(O3,O1,"Y") & " Years, " & DATEDIF(O3,O1,"YM") & " Months, " & DATEDIF(O3,O1,"MD") & " Days"

  • To get the total days formula is in O7:

    =-INT(O3-O1) & " Days"

  • If you want to calculate total hours then formula in cell O9:

    =O1-O3

  • Applied format on cell O9 is, [h]:mm:ss, where [h] evaluates data as 24 Hrs time.

:Edited:

Through comments OP has disclosed that, the exact need is to find difference between two Date Timestamps in regard of Years, Months, Days & Hours, Minutes, Seconds.

For that, I would like to suggest two possible formula.

enter image description here

  • Formula in cell O14:

    =DATEDIF(O12,NOW(),"Y")&" Years, "&DATEDIF(O12,NOW(),"YM")&" Months, "&DATEDIF(O12,NOW(),"MD")&" Days,  "&TEXT(NOW()-O12,"h"" hrs ""m"" mins ""s"" secs""")

  • Formula in cell O16:

     =INT(NOW()-O12)&" days "&TEXT(NOW()-O12,"h"" hrs ""m"" mins ""s"" secs""")

Note:

  • I've used function NOW() for current Date & Time, which is editable.

  • Remember function NOW() is volatile, so to avoid frequent change in current Date & Time, better replace NOW() with any CURRENT DATE & TIME value.

Adjust cell references in the formula as needed.

Improve this answer
12
  • Hi @Curt ,, now check my post I've suggested few formula for different situations,, and I'm sure this is what you were looking for. If my post solves the issue then You may mark it as Answer as well Up vote ☺
    – Rajesh Sinha
    Nov 26, 2020 at 7:34
  • Plzz write your concern that why this has been down voted !!
    – Rajesh Sinha
    Nov 26, 2020 at 8:23
  • Hi Rajesh: the formula in O5 is what I was trying to do but with the amount of HH:MM:SS as well, is there now way to do this, for instance I know the year, month, day, hour, minute and second My daughter was born and I want excel to subtract it from the current year, month, day, hour, minute and second and give me the difference in year, month, day, hour, minute and second. Is there no way to accomplish this?
    – Curt
    Nov 27, 2020 at 2:22
  • I did not down vote you btw
    – Curt
    Nov 27, 2020 at 2:23
  • @Curt, thanks for responding me,, Now check Edited part of my post, solves the issue.
    – Rajesh Sinha
    Nov 27, 2020 at 6:13

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .