How to Calculate Working Hours and Overtime in Excel
This article explains how to calculate daily working hours based on start time, end time, and break time in Excel, and how to determine overtime for hours exceeding the standard working time of 8 hours. By leveraging Excel formulas, you can accurately track working hours and overtime for daily time management.
Basic Setup
First, prepare columns for start time, end time, break time, working hours, and overtime. In this example, the standard working time is set to 8 hours. Any time exceeding 8 hours (excluding break time) is considered overtime.
Example
In the following table, we will calculate working hours and overtime based on the data in columns A to E:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Start Time | End Time | Break Time | Working Hours | Overtime |
| 2 | 09:00 | 18:30 | 1:00 | =B2-A2-C2 | =IF(D2 > TIME(8, 0, 0), D2-TIME(8, 0, 0), 0) |
*In this example, the standard working hours are 8 hours, with a shift from 9:00 AM to 6:30 PM and a 1-hour break.
Calculating Working Hours
Working hours are calculated by subtracting the start time from the end time and then subtracting the break time. Use the following formula:
Working Hours Formula
Enter the following formula in cell D2:
=B2-A2-C2
This formula calculates working hours by subtracting the start time (A2) and break time (C2) from the end time (B2). Format the cell as [h]:mm to display the result in hours and minutes.
Calculating Overtime
Overtime is calculated as the time exceeding the standard 8 hours. If the working hours (D2) exceed 8 hours, the excess is displayed in E2 as overtime.
Overtime Formula
Enter the following formula in cell E2:
=IF(D2 > TIME(8, 0, 0), D2-TIME(8, 0, 0), 0)
This formula checks if the working hours (D2) exceed 8 hours (expressed as TIME(8, 0, 0)). If so, it calculates the excess time as overtime; otherwise, it returns 0.
Formatting Cells – Handling Decimal Results
To display results correctly, format the Working Hours and Overtime cells as [h]:mm:
- Select the range of cells showing working hours and overtime (D2:E2).
- Right-click and select “Format Cells.”
- In the “Number” tab, select “Custom” and enter [h]:mm, then click “OK.”
Conclusion
This article explained how to calculate working hours and overtime in Excel. Working hours are calculated as “End Time – Start Time – Break Time,” and overtime as “Working Hours – Standard Working Time (8 hours).” By automating time tracking with formulas, you can streamline your daily work management and reduce administrative effort.