Convertin hours into days, hours and minutes

  • Thread starter Thread starter Jock
  • Start date Start date
J

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?
 
Use
=INT((Q5))&" day "&TEXT(Q5,"hh"" hours"" mm"" minutes""")

why are you dividing by 7.5? and multiplying by 24?
 
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
 
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 "
 
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
 
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)
 
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...
 
Back
Top