Forum Discussion
Solved
Splitting fractions from whole numbers
Can anyone help me, I want split a number of hours in to weeks, days and hours in three seperate fields, rather do a simple divide that will give me a decimal answer. Thank you
Let's say you have a number of hours in D2.
The number of weeks is =QUOTIENT(D2,168)
The number of days is =QUOTIENT(MOD(D2,168),24)
The number of hours is =MOD(D2,24)
These formulas can be filled down if required.
Let's say you have a number of hours in D2.
The number of weeks is =QUOTIENT(D2,168)
The number of days is =QUOTIENT(MOD(D2,168),24)
The number of hours is =MOD(D2,24)
These formulas can be filled down if required.
This is your method so I am replying to you.
Worksheet formula = SplitByUnitsλ(targetList) SplitByUnitsλ(target) = LET( multiples, {52,7,24}, Quotientλ, LAMBDA(x, y, QUOTIENT(x, y)), lower, SCAN(PRODUCT(multiples), multiples, Quotientλ), upper, lower * multiples, values, QUOTIENT(MOD(target, upper), lower), values )- I have a reverse question.
My Time Clock gives us hours in decimals i.e. 44.83 hours in decimals
The math and proof is below.
Question is how do I program that in Excel whereby I can simply place the decimal hours into a cell and the result show up converted to hours in time 45.38333 in another cell?
Employee Name 9 44.83 403.47 403.47 {83/60} 1.383333333 45.38333 408.45 (4.98)The formula that does this is
= DOLLARDE(decimalHours,60)Divide by a further factor of 24 to convert this to days (formatted in the final column)
Thanks! ROFL
- Perfect, thank you very much
