Excel Timesheet Formula: Calculate Total Hours

Gregory Jul 09, 2026

Tracking work hours has never been more efficient than with Excel's powerful formula capabilities. When it comes to calculating total hours, Excel provides a straightforward solution using timesheet data. Let's delve into the world of Excel timesheet formulas to help you total hours with ease.

the weekly time sheet is shown in red and green
the weekly time sheet is shown in red and green

Before we dive into the formulas, ensure your timesheet data is structured correctly. Typically, timesheets contain columns for Date, Start Time, End Time, and Breaks. Having a consistent format will streamline your calculations.

The 13 Best Timesheet Templates to Track Your Hours
The 13 Best Timesheet Templates to Track Your Hours

Calculating Daily Hours

The first step in totaling hours is to calculate the daily work hours. This involves subtracting the start time from the end time and adjusting for breaks.

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

Here's a simple formula to calculate daily hours in Excel:

=(End Time - Start Time) - Breaks

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

Using Time Format

To ensure accurate calculations, format your time data as Time in Excel. This can be done by right-clicking the column header, selecting 'Format Cells', then 'Custom' and entering the time format (e.g., [h]:mm).

For example, if your Start Time is '8:00' and End Time is '17:00', with a 1-hour break, the formula would be:

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

=(17:00 - 8:00) - 1

Handling Non-Standard Hours

For shifts that span midnight, Excel's time subtraction will result in a negative value. To handle this, you can use the MOD function to find the remainder when the duration is divided by 24:

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

=MOD((End Time - Start Time) - Breaks, 24)

Totaling Hours Across Multiple Days

Staff Time Tracker Excel Template | Employee Attendance & Overtime Sheet
Staff Time Tracker Excel Template | Employee Attendance & Overtime Sheet
How to Calculate Hours Worked and Overtime Using Excel Formula - ExcelDemy
How to Calculate Hours Worked and Overtime Using Excel Formula - ExcelDemy
How to Convert Hours into Minutes | convert hours into minutes in Excel | Excel Tutorials |Excel2022
How to Convert Hours into Minutes | convert hours into minutes in Excel | Excel Tutorials |Excel2022
Time Sheet Template with Breaks
Time Sheet Template with Breaks
The 13 Best Timesheet Templates to Track Your Hours
The 13 Best Timesheet Templates to Track Your Hours
Add Time enter for Timesheet
Add Time enter for Timesheet
Monthly Excel Timesheet Template | Editable & Automated Work Hour Tracker | Job Log Spreadsheet | Instant Download
Monthly Excel Timesheet Template | Editable & Automated Work Hour Tracker | Job Log Spreadsheet | Instant Download
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
Top 21 Excel Formulas
Top 21 Excel Formulas
Weekly time sheet template | Time card in Excel | Employees Time sheet in Excel | learning Center
Weekly time sheet template | Time card in Excel | Employees Time sheet in Excel | learning Center
the excel time and date sheet
the excel time and date sheet
Free Timesheet Template for Excel | Budget Spreadsheet Types By Timeframe, Excel Bill Payment
Free Timesheet Template for Excel | Budget Spreadsheet Types By Timeframe, Excel Bill Payment
robot88 : Situs Game Online  Deposit Pulsa dengan potongan Terbaik
robot88 : Situs Game Online Deposit Pulsa dengan potongan Terbaik
ROTA Template
ROTA Template
Weekly Employee Timesheet: Excel Template (Digital Download)
Weekly Employee Timesheet: Excel Template (Digital Download)
How to calculate work hours in Excel
How to calculate work hours in Excel
how to calculate hours worked
how to calculate hours worked
robot88 : Situs Game Online  Deposit Pulsa dengan potongan Terbaik
robot88 : Situs Game Online Deposit Pulsa dengan potongan Terbaik

Once you've calculated daily hours, summing them up is a breeze. Use the SUM function to total hours across multiple days:

=SUM(Daily Hours)

Excluding Weekends and Holidays

To exclude weekends and holidays from your total, you can use the IF function along with Excel's built-in functions like ISOWEEKNUM and NETWORKDAYS:

=IF(AND(WEEKDAY(Date)<>1,NETWORKDAYS(Date, TODAY())>0), Daily Hours, 0)

This formula checks if the date is not a weekend and not a holiday, then includes the daily hours; otherwise, it excludes them.

Displaying Total Hours in Hours:Minutes Format

To display your total hours in a user-friendly format, use the TEXT function to convert the sum into hours:minutes:

=TEXT(SUM(Daily Hours), "[h]:mm")

With these formulas, you're equipped to efficiently track and total hours in your Excel timesheets. Regularly reviewing and updating your totals will help maintain an accurate record of your work hours.