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

Copper Contributor

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 minutes to complete a task one day and the next week say it took 2 days 2 hours and 40 minutes to complete the same task, I need comparison of the two lengths of time in time format and percentage.  I am not forced to used the format shown. I just want to keep it simple.  Willing to change format fi necesssary. I have these blocks in text so that it doesn't autoformat to time and date.

 

 

 

7 Replies

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

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

@Hans Vogelaar

 

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

LouShawda10_0-1710335837027.png

 

 

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")))

 

 

 

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

@HansVogelaar 

 

 It corrected H7 but I7 remained the same so should I be changing it also.

LouShawda10_0-1710338537167.png

 

 

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)))

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

@HansVogelaar 

Yes needed that col f comparison. It worked.

 

LouShawda10_0-1710339707456.png