Working With Times Example Page

Working With Overtime Hours In Excel

Excel is often used to manage employee time records -- number of hours worked, meal breaks, regular and overtime pay, and so on. This page examines a number of related formulas for a simple daily timekeeping record.

Suppose we have 5 employees, and we want to keep track of the time each comes into work, breaks for lunch, returns from lunch, and leaves for the day. Also, we need to keep track of the regular and overtime hours for each employee, and pay the the overtime hours at a different rate. The sample below shows what the worksheet might look like:

Now, lets look at the values and formulas in each column. Column B is simply the Name of each employee. Columns C, D, E, and F are the times that each employee started work, left for lunch, returned from lunch, and left work for the day. Pay attention to the hours worked by Emma, in row 8. Her shift extended over midnight (she started at 10PM and left at 7AM), and her lunch break also extended over midnight. Our formulas will take this into account, and calculate the proper times.

Cell H2 contains the number of regular, non-overtime, hours per shift. Here, the 8 indicates that a normal shift is 8 hours. Cell K2 contains the rate at which overtime hours are paid. The 1.5 indicates that overtime is paid at time-and-a-half.

The total hours are in column G. This is the difference between the start time in column B and the end time in column F. This value does not pay any attention to the time taken for lunch -- we'll do that later. The formula in G4 is:

=(F4-C4+(F4<C4))*24

This formula subtracts the start time in C4 from the end time in F4. Then, it compares the end time to the start time. If F4 is less than, or earlier than, C4, the expression (F4<C4) will evaluate to True or 1. If not, it will evaluate to False or 0. Adding this comparison result will calculate the number of hours properly in the case where the start time is later in the day than the end time. This is exactly the situation with Emma. Finally, the result is multiplied by 24, to get the actual number of hours, rather than a time value -- e.g., 9.00 rather 9:00. (Since times are stored as a fraction of a 24-hour day, multiplying a time by 24 will return the number of hours).

NOTE: You only need to use the +(F4<C4)part of the formula when the start and end times are just times, without a date. If you are entering a complete date and time, then you don't need the comparison formula, because the date part of the value will allow Excel to properly calculate the difference.

Column H contains the number of hours that were actually worked, after taking out the time taken for lunch. The formula in H4 is:

=((F4-C4+(F4<C4))-(E4-D4+(E4<D4)))*24

This formula is very similar to the formula in G4, expect that it subtracts the time between D4 and E4, the start and end times of the lunch break. Again, we compare the start and end times to ensure that the number of hours is calculated properly if the time period crosses midnight. Look at Emma's hours again -- she left for lunch at 11PM and returned at 12:30 AM. Multiplying the result by 24 gives us the actual number of hours worked, rather than a time value.

Column I contains the number of regular hours worked by each employee. If the actual worked hours in column H is less than the Regular Hours value in H2, we want to use the actual number of worked hours. However, if the actual worked hours in column H is greater than the Regular Hours value in H2, we only want to count H2 hours as regular time. The formula in I4 is:

=MIN($H$2,H4)

This formula simply takes the minimum of the allowed regular hours in H2 and the number of hours actually worked in H4.

Column J contains the number of overtime hours for each employee. If the employee worked less than the allowed regular hours, the overtime hours will, of course, be zero. However, if they worked more than the allowed regular hours, the overtime hours will be the difference between the hours actually worked and the allowed regular hours. The formula in cell J4 is:

=MAX(0,H4-$H$2)

This is simply the maximum of 0 and the difference between actual hours and allowed regular hours.

Column K is just the hourly wage for each employee. Of course, these may be different for each employee, but they are the same in our example. Column L is the amount of regular, non-overtime wages paid. This is just the product of column I, the regular hours, and column K, the hourly wage. Cell L4 has the formula:

=I4*K4

Column M is the amount of overtime wages paid. This is the product of column J, the overtime hours, column K, the hourly wage, and cell K2, the rate at which overtime hours are paid. Cell M4 has the formula:

=J4*K4*$K$2

Finally, column N is just the total wages paid, the sum of the regular and overtime wages. Cell N4 has the formula:

=L4+M4

The worksheet and formulas are summarized below: