Forum Discussion

LouShawda10's avatar
LouShawda10
Copper Contributor
Feb 22, 2024

Using excel to calculating time differences between days, hours & mins (no actual date associated)

I am collection data that is capturing how many days, hours and minutes a task requires.  I need to compare the data from one data field to the next. So if a tech tells me it took 1 day 1 hour and 30...
excel
HansVogelaar's avatar
HansVogelaar
MVP
Feb 22, 2024

LouShawda10 

Let's say the first "time" is in B3 an the second one in C3.

In D3:

 

=LET(time1, SUMPRODUCT(TEXTSPLIT(B3, ":")*{1440,60,1}), time2, SUMPRODUCT(TEXTSPLIT(C3, ":")*{1440,60,1}), d, time1-time2, days, QUOTIENT(d, 1440), hours, QUOTIENT(MOD(d, 1440), 60), minutes, MOD(d, 60), TEXT(days, "00")&":"&TEXT(hours, "00")&":"&TEXT(minutes, "00"))

 

In E3:

 

=LET(time1, SUMPRODUCT(TEXTSPLIT(B3, ":")*{1440,60,1}), time2, SUMPRODUCT(TEXTSPLIT(C3, ":")*{1440,60,1}), d, time1-time2, d/time1)

 

Format E3 as a percentage.

Fill down from D3:E3.

  • LouShawda10's avatar
    LouShawda10
    Copper Contributor
    Feb 22, 2024

    HansVogelaar Thank you so very much with a few tweaks it worked.

    • LouShawda10's avatar
      LouShawda10
      Copper Contributor
      Mar 13, 2024

      @Hans Vogelaar

       

      Having an issue with the formulas...cell H7 is not calculating correctly although it match the formula in cell H6. 

       

       

      H6 =IFS(F6="00:00:00","00:00:00",G6="00:00:00","00:00:00",G6<>"00:00:00",LET(time1, SUMPRODUCT(TEXTSPLIT(G6, ":")*{1440,60,1}), time2, SUMPRODUCT(TEXTSPLIT(F6, ":")*{1440,60,1}), d, time1-time2, days, QUOTIENT(d, 1440), hours, QUOTIENT(MOD(d, 1440), 60), minutes, MOD(d, 60), TEXT(days, "00")&":"&TEXT(hours, "00")&":"&TEXT(minutes, "00")))

       

      H7 =IFS(F7="00:00:00","00:00:00",G7="00:00:00","00:00:00",G7<>"00:00:00",LET(time1, SUMPRODUCT(TEXTSPLIT(G7, ":")*{1440,60,1}), time2, SUMPRODUCT(TEXTSPLIT(F7, ":")*{1440,60,1}), d, time1-time2, days, QUOTIENT(d, 1440), hours, QUOTIENT(MOD(d, 1440), 60), minutes, MOD(d, 60), TEXT(days, "00")&":"&TEXT(hours, "00")&":"&TEXT(minutes, "00")))

       

       

       

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Mar 13, 2024

        LouShawda10 

        I hadn't taken negative differences into account.

        In H6:

         

        =IFS(F6="00:00:00","00:00:00",G6="00:00:00","00:00:00",G6<>"00:00:00",LET(time1, SUMPRODUCT(TEXTSPLIT(G6, ":")*{1440,60,1}), time2, SUMPRODUCT(TEXTSPLIT(F6, ":")*{1440,60,1}), d, ABS(time1-time2), s, IF(time1<time2, "-", ""), days, QUOTIENT(d, 1440), hours, QUOTIENT(MOD(d, 1440), 60), minutes, MOD(d, 60), s&TEXT(days, "00")&":"&TEXT(hours, "00")&":"&TEXT(minutes, "00")))

         

        Fill down.

Resources