Daily over time past 3pm

AppSheet AppSheet Q&A
1

Total Hours = [Time out] - [Time In]

[Normal hours] = If(([Total Hours] < [8 Hour Day]), [3PM Rule], 008:00:00

[Overtime Hours] = If(([Total Hours] > [8 Hour Day]),([Total Hours]-008:00), 00:00:00

[3PM rule] = If(([Time Out]>15:01:00),([Time Out]-003:00:00),([Time Out]-[Time In]))

I’m hoping someone can tell me how unnecessarily complicated and broken my math/thinking is here. In one day I might have multiple time card entries all on different jobs, none of which are more than 8hrs, but one goes past 3pm.

let’s say

job 1 7AM-12pm , job 2 12PM - 1:30PM , job 3 1:30PM - 4PM

How can I make the math jive to produce normal hours for all except job 3, where it has 1.5 [Normal Hours] and 1 [Over Time Hours]. While a single 9Hr entry (unrelated day) still has 8 [Normal Hours] and 1 [Over Time Hours]

I’m still very new to AppSheet and learning, and help out advice here would be greatly appreciated.

2

Please explain the “3PM Rule” in plain language.

3

Is overtime computed only per job? Or per day?

Can an employee work more than one job a day?

Can a job span days? Start one day and end the next?

Is there a weekly overtime?

4

I was trying to accommodate an entry that was less than 8Hr but would count anything past 3PM as Overtime. The day overall would be over 8Hr, but the overtime needs to be assigned to that specific job on that day.

so 12pm-4pm would be 3 normal hours and 1 overtime hour. Below is my newest thought, and would include starting times earlier than 7am

Total Hours = [Time Out] - [Time In]

Am Overtime = If(([Start Time] < 007:00), (007:00 - [Start Time]), 000:00:00

PM Overtime = If(([End Time] > 015:00), ([End Time] - 015:00), 000:00:00

Overtime Hours = [AM Overtime] + [PM Overtime]

Normal Hours = If([Overtime Hours] = 0, [Total Hours], ([Total Hours] - [Overtime Hours]))

1 Like
5

So normal time is paid for any hours between 7am and 3pm (which is itself only 8 hours), and overtime is paid for hours outside that range?

6

Right. 8hr isn’t necessarily required for overtime, but would be rare. Anything outside of normal business hours is overtime.

7

Maybe this?

Total Duration:

([Time Out] - [Time In])

Early Duration:

IFS(
  ([Time Out] <= "07:00:00"),
    [Total Duration],
  ([Time In] < "07:00:00"),
    ("07:00:00" - [Time In]),
  TRUE,
    "000:00:00'
)

Late Duration:

IFS(
  ([Time In] >= "15:00:00"),
    [Total Duration],
  ([Time Out] > "15:00:00"),
    ([Time Out] - "15:00:00"),
  TRUE,
    "000:00:00'
)

Overtime Duration:

([Early Duration] + [Late Duration])

Normal Duration:

([Total Duration] - [Overtime Duration])

Total Hours:

(ROUND(TOTALHOURS([Total Duration]) / 15.0) * 15.0)

Overtime Hours:

(ROUND(TOTALHOURS([Overtime Duration]) / 15.0) * 15.0)

Normal Hours:

(ROUND(TOTALHOURS([Normal Duration]) / 15.0) * 15.0)
2 Likes
8

Or, more concisely:

Total Hours:

(ROUND(TOTALHOURS([Time Out] - [Time In]) / 15.0) * 15.0)

Overtime Hours:

(
  ROUND(
    TOTALHOURS(
      IFS(
        ([Time Out] <= "07:00:00"),
          ([Time Out] - [Time In]),
        ([Time In] < "07:00:00"),
          ("07:00:00" - [Time In]),
        TRUE,
          "000:00:00'
      )
      + IFS(
        ([Time In] >= "15:00:00"),
          ([Time Out] - [Time In]),
        ([Time Out] > "15:00:00"),
          ([Time Out] - "15:00:00"),
        TRUE,
          "000:00:00'
      )
    )
    / 15.0
  )
  * 15.0
)

Normal Hours:

([Total Hours] - [Overtime Hours])
1 Like
9

Huge help, thank you. It covers all three scenarios just as I wanted.

2 Likes