Convertin hours into days, hours and minutes

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?
 
S

Sheeloo

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

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

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
 
H

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

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
 
D

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top