Convert a number to Hours, Minutes, etc.

B

berniean

Hi,
How do I take a number, e.g., 170.5, and covert it to a weeks, days, hours,
and minutes, based on an 8 hour day and a 40 day week? Simple math just gives
me more decimals. I don't want 4.2625 weeks, I want 4 weeks, 1 day, 2 hours,
30 minutes. I want to be able to do this for any number using a formula or
group of formulas.

All help is greatly appreciated.

Thanks,
Bernie
 
D

Dave Peterson

How about:
=INT(A1/40)&" weeks "&INT(MOD(A1,40)/8)&" days "&INT(MOD(A1,8))&" hours "
& INT(MOD(A1,1)*60)& " minutes"
 
A

AltaEgo

In parts:

=INT(A1/40) & " weeks"
=INT(MOD(A1,40)/8) & " days"
=ROUND(MOD(A1,8),0) & " hours"
=ROUND((A1-INT(A1))*60,0) & " minutes."


Putting it all together:

=INT(A1/40) & " weeks, " & INT(MOD(A1,40)/8) & " days, "&
ROUND(MOD(A1,8),0)&" hours, " & ROUND((A1-INT(A1))*60,0) &" minutes."
 
B

berniean

Thanks Dave and Steve. I was playing around with INT, but not MOD, so
couldn't get it to work. Your solution works great.
 

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