Forum Discussion
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 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
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.
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")))
