0

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:

DAY START

06/09/2022 09:00

DAY END

06/09/2022 17:00

HOURS WORKED

6:59:37.929 (Ideally we should have 7 hours here)

This allows for 1 hour lunch break - time(1, 0)

3 replies

null
    • Fred
    • 2 yrs ago
    • Reported - view

    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.

      • Alan_Cooke
      • 2 yrs ago
      • Reported - view

      Fred PERFECT!  I have been reading your responses to a number of folks on this forum and I have to say your involvment here is very much appreciated. I just need to minus one hour to take into account midday break

      • Fred
      • 2 yrs ago
      • Reported - view

      Thank you for the kind words.

      I do take into account a "lunch period" with Lines 3 - 10. But if it is consistent 1 hour for you then a simple

      step2 -1

      can also work.

Login to reply

Content aside

  • 2 yrs agoLast active
  • 3Replies
  • 84Views
  • 2 Following