Mastering Excel: Create a Timesheet with Overtime

Gregory Jul 09, 2026

Creating a timesheet in Excel to track your work hours, including overtime, can be a straightforward process. This not only helps you keep a record of your time but also simplifies invoicing and payroll processes. Let's dive into the step-by-step guide to create an efficient timesheet in Excel with overtime tracking.

FREE Excel Timesheet Template [DOWNLOAD]
FREE Excel Timesheet Template [DOWNLOAD]

Before we start, ensure you have Microsoft Excel installed on your computer. For this guide, we'll use Excel 2016, but the steps are similar in other versions. Let's begin by setting up the basic structure of your timesheet.

how to create a time sheet in excel with overtime  Part 1
how to create a time sheet in excel with overtime Part 1

Setting Up the Basic Timesheet Structure

Open a new or existing workbook in Excel. In the first row, starting from column A, enter the following headers: "Date", "Project/Task", "Start Time", "End Time", "Hours Worked", and "Overtime".

Employee Time Tracking in Excel (+ video tutorial!)
Employee Time Tracking in Excel (+ video tutorial!)

Format the "Date" column as an Excel date, and the "Hours Worked" and "Overtime" columns as decimal numbers. You can do this by right-clicking the column header, selecting "Format Cells", and choosing the appropriate category.

Entering Daily Work Hours

Excel Time Sheet with Overtime 😍 Easy Formula (Beginner to Pro)
Excel Time Sheet with Overtime 😍 Easy Formula (Beginner to Pro)

Starting from row 2, enter the date, project/task, start time, and end time for each workday. You can use the time format (HH:MM) for start and end times. Excel will automatically calculate the hours worked based on the start and end times.

To calculate hours worked, use the following formula in cell C2 (assuming your data starts from row 2): `=IFERROR(TIME(D2-B2,"H":MM"),0)`. This formula subtracts the start time from the end time and displays the result in hours and minutes format. If the end time is earlier than the start time, it will display 0.

Tracking Overtime Hours

How to Create a Timestamp in Excel
How to Create a Timestamp in Excel

To track overtime, set a threshold for regular hours, e.g., 8 hours. In cell F2, enter the following formula to calculate overtime: `=IF(D2>8,D2-8,0)`. This formula subtracts 8 from the total hours worked if it's greater than 8, giving you the overtime hours.

You can adjust the threshold in the formula to match your organization's overtime policy. Format the "Overtime" column as currency or decimal, depending on your preference.

Formatting and Customizing Your Timesheet

How to Make a Time Sheet in Excel (with Formulas and Templates)
How to Make a Time Sheet in Excel (with Formulas and Templates)

Now that you have the basic structure and formulas set up, you can customize your timesheet to fit your needs. Add filters to easily sort and search through your data. To do this, click anywhere in your data, go to the "Data" tab, and select "Filter" in the "Sort & Filter" group.

You can also add a summary row at the bottom to display totals for hours worked and overtime. Use the "SUBTOTAL" function to add up the hours and overtime. For example, in cell F20 (assuming your data ends in row 19), enter `=SUBTOTAL(9,F$2:F$19)` to calculate the total hours worked, and `=SUBTOTAL(9,G$2:G$19)` to calculate the total overtime.

Timesheet Complete Software Excel | Total Hour Total Over time and Pay
Timesheet Complete Software Excel | Total Hour Total Over time and Pay
Weekly Time Sheet, Employee Time Tracker, Clock in and Out Timesheet, Freelancer Template, Trucking Time Sheet Template Excel Spreadsheet - Etsy
Weekly Time Sheet, Employee Time Tracker, Clock in and Out Timesheet, Freelancer Template, Trucking Time Sheet Template Excel Spreadsheet - Etsy
Excel - how to create timesheets
Excel - how to create timesheets
Staff Time Tracker Excel Template | Employee Attendance & Overtime Sheet
Staff Time Tracker Excel Template | Employee Attendance & Overtime Sheet
Elapsed Time Excel
Elapsed Time Excel
How to add time with hours/minutes/seconds increments in Excel?
How to add time with hours/minutes/seconds increments in Excel?
2.2M views · 22K reactions | Time Worked in Excel! #tutorial | CheatSheets | Facebook
2.2M views · 22K reactions | Time Worked in Excel! #tutorial | CheatSheets | Facebook
How to Calculate Hours Worked and Overtime Using Excel Formula - ExcelDemy
How to Calculate Hours Worked and Overtime Using Excel Formula - ExcelDemy
a spreadsheet showing the company name and timesheet
a spreadsheet showing the company name and timesheet
Payroll Templates | Microsoft Create
Payroll Templates | Microsoft Create
the excel time and date sheet
the excel time and date sheet
Work Timesheet Excel | Templates at allbusinesstemplates.com
Work Timesheet Excel | Templates at allbusinesstemplates.com
a screenshot of the workdays formula in excel
a screenshot of the workdays formula in excel
Excel Timesheet Template; Auto Calculate Overtime, Double Time, Printable time sheet
Excel Timesheet Template; Auto Calculate Overtime, Double Time, Printable time sheet
How to Make an Availability Schedule in Excel (with Easy Steps) - ExcelDemy
How to Make an Availability Schedule in Excel (with Easy Steps) - ExcelDemy
a blackboard with the words create a dataform in excel
a blackboard with the words create a dataform in excel
Daily Work Tracker Excel: Essential Functions for Better Planning
Daily Work Tracker Excel: Essential Functions for Better Planning
Daily Work Schedule Checklist Template in Excel
Daily Work Schedule Checklist Template in Excel
Boost Productivity With 141 Free Sheets
Boost Productivity With 141 Free Sheets
Calender in Excel ‼️ Amazing Excel trick using data validation and conditional formatting ✅ #Excel
Calender in Excel ‼️ Amazing Excel trick using data validation and conditional formatting ✅ #Excel

Creating a PivotTable for Analysis

A PivotTable can help you analyze your time data more effectively. Select your data, go to the "Insert" tab, and click "PivotTable". Choose where you want to place the PivotTable and click "OK". In the "PivotTable Fields" pane, drag and drop the "Date", "Project/Task", "Hours Worked", and "Overtime" fields into the appropriate areas to create a summary of your time data.

You can customize the PivotTable by adding filters, sorting, and formatting to make it more readable and informative.

With these steps, you now have an efficient timesheet in Excel that tracks your regular hours and overtime. Regularly update your timesheet, and you'll have a clear record of your work hours, making invoicing and payroll processes smoother. Happy tracking!