Calculating Weekly Overtime

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I need to calculate Regular Hours and Overtime Hours over the course of a 7-day period. I have 3 tables - Employee, Time Card and Calendar. Employee and Calendar are the Parents, Time Card is the child. 

  • Parent Fields - Employee: Full Name, Employee ID #, Hourly Rate
  • Parent Fields - Calendar: Date, Week #
  • Child Fields: Time In, Time Out, Total Hours (Duration Calculation), Total Labor $

Solving For:

  • For each week #, I need a running summary of the EE's total hours worked for the week - referenced as WTD below.
  • For example, the week starts on a Sunday
    • Sunday - the EE worked 8 hours; WTD: 8 hours
    • Monday - 10 hours: WTD 18 hours
    • Tuesday - 12 hours: WTD: 30 hours
    • Wednesday - 8 hours: WTD 38 hours
    • Thursday - 12 hours: WTD 50 hours 
  • Sunday to Wednesday, all hours would be calculated at the regular rate of pay and be considered regular hours
  • On Thursday, the EE exceeds 40 hours, so I would need to calculate 2 regular hours, and the remaining 10 hours at an overtime rate of pay.

I am lost  and could use some help on this one ....

Photo of Andrew


  • 0 Points

Posted 3 years ago

  • 0
  • 1
There is an easy solution here.

Make a formula checkbox field on the time card record for a field called [Current Week?]
The formula would be

FirstDayOfWeek(Today()) = FirstDayOfWeek([Date])

Also make a similar field called [Last Week?]

FirstDayOfWeek(Today() - Days(7)) = FirstDayOfWeek([Date])

Then you can make a pair of Summary fields on the Relationship between Employees and TimeCards to sum up total hours worked Current Week and Last Week based on a filter of those respective checkboxes being true.

So now at any time you know how many total hours during the current and previous week and you can do your formulas to calculate regular and OT hours.