Convertin hours into days, hours and minutes

[Jock]

Common question and I've looked at a number of different answers in the
forum. None quite work for me though.
I can get the days part ok using INT, its the leftover hours and minutes
which is giving me grief" The following nearly gets there:
=INT((Q5/7.5)*24)&" day "&TEXT(MOD(Q5,7.5),"hh"" hours"" mm"" minutes""")
However with 15:13 (hh:mm) in cell Q5, the result is 2 d 15h 13m rather than
2d 0h 13m

Any ideas?

 

[Sheeloo]

Use
=INT((Q5))&" day "&TEXT(Q5,"hh"" hours"" mm"" minutes""")

why are you dividing by 7.5? and multiplying by 24?

 

[Jock]

Because there's 7½ hours in a working day. Here anyway. Therefore 15 hours
and 13 minutes will equate to 2 (working) days and 13 minutes i.e 2d 0h 13m

 

[HARSHAWARDHAN. S .SHASTRI]

Dear Jock,

Following formula will solve your problem

=INT(LEFT(TEXT(Q5,"hh:mm"),2)/7.5)&"d
"&INT((MOD(LEFT(TEXT(Q5,"hh:mm"),2),7.5)*60+MID(TEXT(Q5,"hh:mm"),4,2))/60)&"h
"&(((MOD(LEFT(TEXT(Q5,"hh:mm"),2),7.5)*60+MID(TEXT(Q5,"hh:mm"),4,2))/60)-INT((MOD(LEFT(TEXT(Q5,"hh:mm"),2),7.5)*60+MID(TEXT(Q5,"hh:mm"),4,2))/60))*60&"m "

 

[Jock]

Thanks for this. It works ok up until 24 hours is exceeded. I am using a
cumulative total of hours and there could be 200 plus hours in Q5.
Thanks for trying though

 

[David Biddulph]

Your MOD function is looking for the excess over 7.5 days. You presumably
intended it to be the excess over 7.5 hours, so it would be MOD(Q5,7.5/24)

 

[Sheeloo]

In that case use
=INT((Q5/7.5)*24)&" day "&TEXT(MOD(Q5,7.5/24),"hh"" hours"" mm"" minutes""")

See David's post for the explanation...