How to Use an Excel Formula to Calculate Overtime and Double Time (3 Methods)

Dataset Overview

Let’s say we have a dataset containing an employee’s Entering and Existing Time on certain Dates. We want to calculate the Overtime and Double Time for that employee.

Differentiate Between Overtime and Double Time

• A full-time employee typically works 8 hours a day as part of normal working hours.
• If an employee works more than 8 hours, we need to distinguish between two scenarios:
  1. Overtime: This refers to the count of working hours beyond the standard 8 hours. Overtime is typically compensated at a rate of 1.5 times the normal working hours.
  2. Double Time: In some states (such as California in the USA), if an employee works more than 12 hours in a day, they are subject to double time. Double time means the hourly rate is twice the normal working hours.

Method 1 – Using IF and MIN Excel Formula

Calculate Working Hours:

• Subtract the Entering Time from the Exiting Time for each date.
• Since Excel displays the result in days, multiply it by 24 to convert to hours:

• Press ENTER and drag the Fill Handle to find the working hours for individual dates.

Calculate Normal Work (NW):

• Insert the following formula in a blank cell (e.g., F7):

• This ensures that employees meet their required working hours (8 hours).

• Press ENTER then drag the Fill Handle to display whether the employee fulfills the normal hours or not.

Calculate Overtime:

• In another cell (e.g., G7), use the following formula:

• Explanation:
  • If Hours Worked > 8, check if it’s less than or equal to 12.
  • If true, subtract 8 hours (normal work hours) from the total.
  • If false, calculate the minimum value between (Hours Worked – 8) and 4 (maximum allowed overtime after 8 hours).
  • If Hours Worked <= 8, display 0 hours as Overtime.

• Press ENTER and drag the Fill Handle to display the Overtime values.

Calculate Double Time:

• Enter the following formula into any cell where you wish to show the Double Time value (e.g., H7):

In this formula, the IF function evaluates whether the working hours are greater than 12. If true, it subtracts 12 from the hours; otherwise, it displays 0.

• Press ENTER and drag the Fill Handle to display the Double Time hours as shown in the following image.

Method 2 – Combined MIN and SUM Functions

In this method, we’ll use the combined MIN and SUM functions to calculate overtime and double time. We’ll assume that the Time Worked hours are already calculated as part of the dataset.

Calculate Normal Working Hours (NW):

• Enter the following formula in any adjacent cell (e.g., F7):

This ensures that the normal working hours do not exceed 8 hours.

• Press ENTER then Drag the Fill Handle to see whether normal working hours are met or not.

Calculate Overtime:

• In another cell (e.g., G7), enter the following formula:

• Explanation:
  • If the total hours worked (E7) minus normal working hours (F7) is greater than 4, consider only 4 hours as overtime.
  • Otherwise, use the actual difference between the two.

• Press ENTER and drag the Fill Handle to show the Overtime hours.

Calculate Double Time:

• Subtract the sum of normal working hours (F7) and overtime (G7) from the total hours worked (E7):

The formula subtracts Normal Work and Overtime from Time Worked hours.

• Press ENTER and drag the Fill Handle to get the Double Time values similar to the picture below:

Method 3 – Excel IF and SUM Excel Formula

In this method, we’ll consider weekly working hours. If an employee works more than 40 hours in a week (5 working days), it’s considered overtime. If the total working hours exceed 60, it’s double time.

Calculate Total Weekly Working Hours:

• Use the SUM function to add up the Time Worked hours for the week (E9:E13):

• Press ENTER to calculate the total working hours.

Calculate Overtime:

• In the Overtime column, insert the following formula (e.g., H9):

• • If the total weekly hours exceed 40, subtract 40 to find the overtime hours.
  • Otherwise, display 0.

Calculate Double Time:

• In the Double Time column (e.g., G9), enter this formula:

• • If the total weekly hours exceed 60, subtract 60 to find the double time hours.
  • Otherwise, display 0.

Calculate Actual Overtime:

When the Time Worked exceeds 65 hours and the total Extra Time amounts to 25 hours, only 20 hours can be designated as Overtime. To calculate the actual Overtime in cell G14, enter the following formula:

This formula ensures that the Overtime is capped at 20 hours, regardless of whether the Total working hours exceed 60 or not.

If you want to display the Double Time along with Time Worked and Overtime, enter =G13 in the G14 cell.

Download Excel Workbook

You can download the practice workbook from here:

<< Go Back to Overtime | Formula List | Learn Excel