Excel Formula: Track Employee Hours In & Out

Gregory Jul 09, 2026

Accurately tracking employee hours is crucial for payroll, project management, and understanding labor costs. Excel, with its robust formula capabilities, simplifies this process. Let's explore how to calculate employee hours in and out using Excel formulas.

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

Before we dive into the formulas, ensure your data is structured with columns for 'In' and 'Out' timestamps. For this example, let's assume your data starts from A2 (In) and B2 (Out).

Staff Time Tracker Excel Template | Employee Attendance & Overtime Sheet
Staff Time Tracker Excel Template | Employee Attendance & Overtime Sheet

Calculating Daily Hours

To find the daily hours worked by an employee, subtract the 'In' time from the 'Out' time.

How to Calculate Hours Worked in Excel
How to Calculate Hours Worked in Excel

Formula: `=B2-A2`

Using Time Format

How to Calculate Hours Worked Minus Lunch Using Excel Formulas (4 Methods) - ExcelDemy
How to Calculate Hours Worked Minus Lunch Using Excel Formulas (4 Methods) - ExcelDemy

If your timestamps are in time format (e.g., 08:00:00), use the `TIME` function to subtract and get the hours worked.

Formula: `=TIME(B2-HOUR(A2),MINUTE(A2)-MINUTE(B2))`

Using Text Format

FREE Calendar Templates
FREE Calendar Templates

If your timestamps are in text format (e.g., 08:00 AM), use the `TIMEVALUE` function to convert to time format before subtracting.

Formula: `=TIMEVALUE(B2)-TIMEVALUE(A2)`

Calculating Total Hours

Employee time sheet with wage and hours included. Excel template with formulas. Instant downdload. Printable. Selfemployee tool.
Employee time sheet with wage and hours included. Excel template with formulas. Instant downdload. Printable. Selfemployee tool.

To find the total hours worked by an employee over a period, use the `SUM` function to add up the daily hours.

Formula: `=SUM(D2:D100)` (Assuming daily hours are in column D)

Calculate work hours
Calculate work hours
Add Time enter for Timesheet
Add Time enter for Timesheet
How to calculate work hours in Excel
How to calculate work hours in Excel
Time Sheet Calculator in Excel
Time Sheet Calculator in Excel
ROTA Template
ROTA Template
Employee Schedule Tracker Spreadsheet Staff Shift Planner Excel Google Sheets Work Schedule Planner Workforce Coverage & Overtime Calculator
Employee Schedule Tracker Spreadsheet Staff Shift Planner Excel Google Sheets Work Schedule Planner Workforce Coverage & Overtime Calculator
Excel Timesheet Calculator Template [FREE DOWNLOAD]
Excel Timesheet Calculator Template [FREE DOWNLOAD]
Calculate Employee Overtime in Google Sheets | Easy Tutorial & Tips
Calculate Employee Overtime in Google Sheets | Easy Tutorial & Tips
How to Calculate Hours Worked and Overtime Using Excel Formula - ExcelDemy
How to Calculate Hours Worked and Overtime Using Excel Formula - ExcelDemy
Employee Attendance Calendar
Employee Attendance Calendar
Employee Absence Schedule Template for Excel | Download
Employee Absence Schedule Template for Excel | Download
Employee Schedule Templates | PDF, Word And Excel
Employee Schedule Templates | PDF, Word And Excel
Employee Overtime Tracker Spreadsheet
Employee Overtime Tracker Spreadsheet
Download Average Training Hours Calculator Excel Template - ExcelDataPro
Download Average Training Hours Calculator Excel Template - ExcelDataPro
Excel roster template: 4‑week shift planner & payroll calculator for small teams
Excel roster template: 4‑week shift planner & payroll calculator for small teams
Work Hours Tracker | Excel & Google Sheets Timesheet Template | Employee Attendance Log | Editable Work Schedule Spreadsheet
Work Hours Tracker | Excel & Google Sheets Timesheet Template | Employee Attendance Log | Editable Work Schedule Spreadsheet
Free Excel Employee Scheduling Template | When I Work
Free Excel Employee Scheduling Template | When I Work
how to calculate hours worked in excel - tips by michael schreck
how to calculate hours worked in excel - tips by michael schreck
Employee Schedule Template – Automate rosters & wages with a 52‑week Excel planner
Employee Schedule Template – Automate rosters & wages with a 52‑week Excel planner
Employee Schedule Excel
Employee Schedule Excel

Using Conditional Formatting

To highlight cells based on hours worked (e.g., red for overtime), use conditional formatting with the `IF` function.

Formula: `=IF(D2>8,"Overtime", "Regular")`

Calculating Overtime Hours

To find overtime hours, compare the daily hours with the standard work hours (e.g., 8 hours).

Formula: `=IF(D2>8,D2-8,0)`

With these Excel formulas, you can efficiently track and analyze employee hours, enabling better resource planning and payroll management. Regularly review and update your formulas to ensure accuracy and adapt to changes in work schedules.