CODEWITHSUNDEEP
google-sheets
HSHO
HSHO

Reputation: 525

Calculating the time and converting into Days, Hours and Minutes

I have been trying to convert the subtracted time to days, hours and minutes but my formula is missing with some functionality which i am unable to track.

I would appreciate your help.

=INT(I4)&" days "&TEXT(I4,"h"" hrs ""m"" mins """)

I have attached a Sheet where i have made all these calculation.

google Sheet

Upvotes: 1

Views: 189

Answers (1)

Martín
Martín

Reputation: 10177

You can try with this formula that checks if each value is existent and adds its correspondent suffix:

=ArrayFormula(LAMBDA(dif,IF(D4:D="","",IF(ROUNDDOWN(HOUR(dif)/9)=0,"",ROUNDDOWN(HOUR(dif)/9)&" days ")
&IF(MOD(HOUR(dif),9)=0,"",MOD(HOUR(dif),9)&" hs.")&IF(MINUTE(dif)=0,"",MINUTE(dif)&" mins")))
(E4:E-D4:D+IF(E4:E<D4:D,1,0)))

=ArrayFormula(LAMBDA(dif,IF(D4:D="","",IF(ROUNDDOWN(HOUR(dif)/9)=0,"",ROUNDDOWN(HOUR(dif)/9)&" days ") &IF(MOD(HOUR(dif),9)=0,"",MOD(HOUR(dif),9)&" hs.")&IF(MINUTE(dif)=0,"",MINUTE(dif)&" mins"))) (E4:E-D4:D+IF(E4:E<D4:D,1,0)))

PS: You can change the 9 to a cell that actually contains the value of the shift, in case it can change

Upvotes: 3

Related Questions