Forum Discussion

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

LouShawda10

Copper Contributor

Feb 22, 2024

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

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
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.
- LouShawda10
  Copper Contributor
  Mar 13, 2024
  HansVogelaar
  
  It corrected H7 but I7 remained the same so should I be changing it also.
  
  I6 =IFS(F6="0:00:00",0%,G6="00:00:00",0%,G6<>"00:00:00",(LET(time1,SUMPRODUCT(TEXTSPLIT(G6,":")*{1440,60,1}),time2,SUMPRODUCT(TEXTSPLIT(F6,":")*{1440,60,1}),d,time1-time2,d/time1)))
  
  I7 = =IFS(F7="0:00:00",0%,G7="00:00:00",0%,G7<>"00:00:00",(LET(time1,SUMPRODUCT(TEXTSPLIT(G7,":")*{1440,60,1}),time2,SUMPRODUCT(TEXTSPLIT(F7,":")*{1440,60,1}),d,time1-time2,d/time1)))
  - HansVogelaar
    MVP
    Mar 13, 2024
    LouShawda10
    
    The formula in column I is still correct.
    
    The decrease in row 7 is 00:00:24, that is 89% of 00:00:27. Since it is a decrease, you get -89%.
    
    Or did you want the percentage of column F? In that case, use d/time2 at the end of the formula instead of d/time1.