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)
- Using online payroll calculators to check the math three different ones returned 44 hours 50 minutes?
I don't understand how or why 44.83 should be converted to 45.83333 - it makes no sense to me.
- You are probably correct the formula CoPilot AI returned is 44 hours 50 minutes.
=INT(G6)&" hours " & ROUND((G6-INT(G6))*60, 0) & " minutes"
However, what we have been doing is:
just dealing with the decimal in 44.83 like this =83/60
then the result 1.38.333 and adding back to the whole number 44 + 1.38333 = 45.38333
which I think is incorrect but that is how we have been doing it.
Please clarify for me the procedure and steps.
Thanks! ROFL
