| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert a number to Hours, Minutes, etc.
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert a number to Hours, Minutes, etc.
How about:
=INT(A1/40)&" weeks "&INT(MOD(A1,40)/8)&" days "&INT(MOD(A1,8))&" hours " & INT(MOD(A1,1)*60)& " minutes" berniean wrote: 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 -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert a number to Hours, Minutes, etc.
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." -- Steve "berniean" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert a number to Hours, Minutes, etc.
Thanks Dave and Steve. I was playing around with INT, but not MOD, so
couldn't get it to work. Your solution works great. "berniean" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Convert Total Hours and Minutes to Whole Number | Excel Worksheet Functions | |||
| Is it possible to convert a number into hours and minutes | Excel Worksheet Functions | |||
| How do I convert a number of minutes into hours and minutes? | Excel Worksheet Functions | |||
| Convert a number of minutes like 400 into hours and minutes | Excel Worksheet Functions | |||
| Convert hours and minutes in time format into fractions of hours.. | Excel Worksheet Functions | |||
Linear Mode
