Hours result from appointment field
I have an appointmenet field which has started giving weird results in a formula providing hours worked.
This is the formula: duration(appointment(datetime('DAY END'), datetime('DAY START'))) - time(1, 0)
The result in "HOURS WORKED" is: 6:05:37.929
How can I adjust the formula above to get a more meaningful result?
Let's say to the nearest half hour.
Here is what was entered:
6:59:37.929 (Ideally we should have 7 hours here)
This allows for 1 hour lunch break - time(1, 0)
Looks like you have two date/time fields (Day Start and Day End), so let us work with what you have.
You can try something like:
let step1 := number('DAY END' - 'DAY START'); let step2 := step1 / 3600000; switch true do case step2 = 6 and step2 < 8: step2 - 0.5 case step2 = 8: step2 - 1 default: step2 end
Line 1 creates a variable (step1) and subtracts the two date/time fields and returns the number of miliseconds between the two date/time.
Line 2 then takes that number and divides by 360,000 so we a number that is in hours.
Lines 3 - 10 uses the switch command to allow us to personalize the number based on US employment hours (I'm guessing here so feel free to modify it to your situation). So anyone who has worked between 6 hours and less than 8 hours gets 0.5 hours subtracted from their time. Anyone who works 8 hours gets 1 hour subtracted from their time. If their hours don't fit into these two categories then we will just display the hours worked.
I hope this helps.
- 1 yr agoLast active