Forum Discussion
Splitting fractions from whole numbers
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
)
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?
From your earlier discussion, I interpreted 44.83 as 48 + 83/60 minutes. That is, I thought the decimal part was to be used as the count of seconds. From what you say here, it is actually to be interpreted as an ordinary decimal number denominated as hours.
From there to convert to days one divides by 24 but formats the result as a time duration [h]:mm.
= hours / 24
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.