Test 6 - Excessive Overtime

So far, we've looked for ineligible employees who receive overtime pay, and employees who receive overtime pay at a higher rate than they should. But just because eligible employees are receiving the correct overtime wage doesn't mean that there aren't any more overtime issues to consider.

Sometimes, organizations will have a policy in place that limits the amount of overtime each employee can work each week. These are typically put in place for financial reasons (to avoid excessive amounts of overtime), and/or to promote employee health and prevent fatigue. Even if your organization doesn't have limit the amount of overtime that an employee can accrue, this is still a good test to run, if only to gather data on which employees work overtime.

Risk 

Excessive overtime results in inflated costs and can present quality and safety concerns due to fatigue.

Objective

Identify any employees who worked overtime on weekends or more than 15 hours per week (Monday-Friday).

Supporting documentation

Overtime Policy

No employee is allowed to work more than 15 hours overtime in any week (Monday - Friday). The business is closed on weekends (Saturday and Sunday).

Analyzing Data

  1. In the TimeCard table, create a character computed field, c_WeekNum, to identify the week number of the year for each date in the file:
    • STRING((CTOD(DATE( timein),"MM/DD/YYYY" ) - (`20140101` )) / 7 + 1 , 2)

    The timein field consists of a date and time. To convert the timein field to a week number field, we need to eliminate the time element. In order to do this, we'll convert the timein field to a character field (DATE( ) function) and then convert it back to a numeric field without the time (CTOD( ) function). Then, we'll subtract Jan 1, 2014 from this result to calculate the number of days between the two dates. We divide the number of days by 7 and add 1 to give us the week number. 

    The reason we add 1 is because of fixed point arithmetic; for example:
    Jan 3 - Jan 1 = 2. 2 ÷ 7 = 0.2857..., which rounds to 0 (week 0). We'll need to add 1 to make it week 1. Dec 18 - Jan 1 = 351. 351 ÷  7 = 50.1428...., which rounds to 50. Adding 1 makes it week 51. Finally, we need to convert the result to a 2-byte character by using the STRING( ) function.

    OPEN TimeCard
    DEFINE FIELD c_WeekNum COMPUTED STRING((CTOD(DATE( timein),"MM/DD/YYYY" ) - (`20140101` )) / 7 + 1 , 2)

  2. In the TimeCard table, calculate the total time worked per week by each employee by Summarizing on the employee number and c_WeekNum fields. Subtotal the c_TimeWorked field. Name the new table t_WeeklyOvertime.

    Show me

    OPEN TimeCard
    DEFINE FIELD c_WeekNum COMPUTED STRING((CTOD(DATE( timein),"MM/DD/YYYY" ) - (`20140101` )) / 7 + 1 , 2)

    SUMMARIZE ON empno c_WeekNum SUBTOTAL c_TimeWorked TO "t_WeeklyOvertime.FIL" PRESORT
    OPEN "t_WeeklyOvertime"

  3. In the t_WeeklyOvertime table, create a computed field, c_Overtime, that calculates the amount of overtime worked in the week based on 40 regular hours per week:
    • c_TimeWorked - 40

    OPEN TimeCard
    DEFINE FIELD c_WeekNum COMPUTED STRING((CTOD(DATE( timein),"MM/DD/YYYY" ) - (`20140101` )) / 7 + 1 , 2)

    SUMMARIZE ON empno c_WeekNum SUBTOTAL c_TimeWorked TO "t_WeeklyOvertime.FIL" PRESORT

    OPEN "t_WeeklyOvertime"

    DEFINE FIELD c_Overtime COMPUTED  c_TimeWorked - 40

  4. Apply a filter to identify issues where an employee worked more than 15 hours of overtime in a week. Extract the results to a new table, r_ExcessiveOvertime.

    • c_Overtime > 15

    OPEN TimeCard
    DEFINE FIELD c_WeekNum COMPUTED STRING((CTOD(DATE( timein),"MM/DD/YYYY" ) - (`20140101` )) / 7 + 1 , 2)

    SUMMARIZE ON empno c_WeekNum SUBTOTAL c_TimeWorked TO "t_WeeklyOvertime.FIL" PRESORT

    OPEN "t_WeeklyOvertime"
    DEFINE FIELD c_Overtime COMPUTED  c_TimeWorked - 40
    EXTRACT IF c_Overtime > 15 TO "r_ExcessiveOvertime"

    OPEN "r_ExcessiveOvertime

Conclusion

Oftentimes, there are valid reasons for excessive overtime. Consider a retail shop over Winter holidays. Typically, this is an extremely busy time for the industry and a popular time for employees to take vacation, leaving the business short-staffed and necessitating excessive overtime. Regardless of overtime limits, excessive overtime can result in compromised quality of work due and decreased employee health.

If there is frequent excessive overtime, particularly if it involves a small number of employees, it may be a result of fraudulent time record tampering. The system used for recording time is a determining factor when considering the likelihood of this. If there are building access key cards, you might compare an employee's time records to their building access records. Consider an employee whose time record shows they worked from 2:00 pm to 8:00 pm, but their building access record shows they entered the building at 1: 55 pm and again at 7: 30 pm. This would indicate that after clocking in at 2:00 pm, they left the building at some point while on the clock.