how to calculate hours worked per week in excel
Learn how to calculate hours worked per week in Excel using formulas for daily shifts, weekly sums, decimal payroll hours, and custom time formats.
To calculate hours worked per week in Excel, subtract the shift start time and any unpaid break duration from the end time for each shift, sum those daily totals across the week using the SUM function, and apply the custom format [h]:mm so total durations exceeding 24 hours do not reset. Alternatively, multiply the daily elapsed time by 24 to convert the result into standard decimal hours suitable for payroll processing and multiply or sum them directly.
Accurate calculation of employee work hours is vital to ensure workers receive proper compensation for their time and effort. Setting up a standardized timesheet structure prevents payroll discrepancies and provides a clear audit trail for weekly work schedules.
Weekly Timesheet Formulas and Formatting Reference Table
When tracking time in Excel, formulas vary depending on whether you want to display time durations in standard hours and minutes or as decimal figures for payroll software. Excel stores all dates and times as numbers representing fractions of a 24-hour day. A duration of 12 hours equals 0.5, while 24 hours equals 1.0. The table below outlines the core formulas, cell number formats, and calculation purposes required to manage a weekly schedule.
| Calculation Goal | Excel Formula Structure | Required Cell Format | Underlying Mathematical Logic |
|---|---|---|---|
| Daily Duration (Time Format) | =(Time_Out - Time_In) - Unpaid_Break |
Time (h:mm AM/PM or hh:mm) |
Subtracts fraction of day worked minus unpaid lunch fraction. |
| Daily Duration (Decimal Hours) | =((Time_Out - Time_In) - Unpaid_Break) * 24 |
Number (2 decimal places, 0.00) |
Multiplies the day fraction by 24 to yield standard payroll hours. |
| Total Weekly Duration | =SUM(Daily_Duration_Range) |
Custom ([h]:mm or [hh]:mm) |
Adds daily time fractions; square brackets prevent the 24-hour rollover. |
| Total Weekly Decimal Hours | =SUM(Daily_Decimal_Range) |
Number (2 decimal places, 0.00) |
Sums decimal values directly for wage multiplication. |
| Average Daily Work Hours | =AVERAGE(Daily_Time_Range) |
Custom ([h]:mm) or Number (0.00) |
Computes arithmetic mean across all logged shift entries. |
| Multi-Day Elapsed Hours | =(End_DateTime - Start_DateTime) * 24 |
Number (0.00) |
Computes difference across calendar boundaries multiplied by 24. |
How the Weekly Timesheet Table Is Calculated
Constructing an operational weekly timesheet requires organizing the workbook layout properly before applying formulas. A standard weekly schedule fits seven rows for shift entries (one row per calendar day of the work week) followed by a summary row for weekly totals. The table columns should include Employee Name, Shift Date, Time In, Time Out, Unpaid Break, and Hours Worked.
Step 1: Establish the Schedule and Shift Dates
Begin by structuring the date column. When you enter a primary start date for the work week, subsequent days and dates across the workdays can be generated automatically using cell references or formulas that increment the calendar day. This ensures that the schedule tracks consecutive workdays cleanly from the start date through the end of the work week.
Step 2: Format Entry Cells for Time
Select the cells designated for punch times (Time In and Time Out). Right-click the selected cells, choose Format Cells, open the Number tab, select the Time category, and choose a standard clock format such as h:mm AM/PM for a 12-hour clock or hh:mm for a 24-hour clock. Enter times consistently with AM or PM designations when using 12-hour formatting to prevent silent calculation errors. To maintain clean entries, use Excel Data Validation set to Time to prevent team members from entering invalid text entries.
Step 3: Calculate Daily Worked Hours
For each workday, subtract the start time from the end time. If unpaid meal breaks are tracked, enter the break duration as a standard time value (such as 0:30 for 30 minutes) and subtract it from the gross shift length. Because Excel measures time as a fraction of a 24-hour day, evaluating Time_Out - Time_In - Break returns a time fraction.
If your organization evaluates payroll in decimal figures, wrap the subtraction in parentheses and multiply by 24: =((Time_Out - Time_In) - Break) * 24. Format this resulting cell as a Number with two decimal places. For example, an 8-hour and 30-minute shift becomes 8.50 decimal hours.
Step 4: Sum the Total Hours for the Week
In the summary row at the bottom of the work week, insert a SUM formula to total the hours for each staff member. If summing decimal columns, the formula =SUM(F2:F8) directly adds the values into a final weekly decimal sum. If summing clock-duration cells, the standard time format resets every 24 hours. To display the true cumulative total across seven workdays, select the total cell, open Format Cells, choose the Custom category, and input [h]:mm or [hh]:mm. The square brackets instruct Excel to suppress the 24-hour reset and display total elapsed weekly hours.
If you want to evaluate daily workload distribution across the workdays, use =AVERAGE(F2:F8) to compute the average hours worked over the recorded period.
Complete Worked Example
Consider an employee, M. Thorne, working five consecutive workdays during a weekly pay period with a scheduled 30-minute unpaid break each day:
Row 2 (Mon, 10/12): Time In = 08:00 AM | Time Out = 04:30 PM | Break = 0:30 | Daily Dec = =((D2-C2)-E2)*24 -> 8.00
Row 3 (Tue, 10/13): Time In = 08:15 AM | Time Out = 05:00 PM | Break = 0:30 | Daily Dec = =((D3-C3)-E3)*24 -> 8.25
Row 4 (Wed, 10/14): Time In = 08:00 AM | Time Out = 04:30 PM | Break = 0:30 | Daily Dec = =((D4-C4)-E4)*24 -> 8.00
Row 5 (Thu, 10/15): Time In = 08:30 AM | Time Out = 05:00 PM | Break = 0:30 | Daily Dec = =((D5-C5)-E5)*24 -> 8.00
Row 6 (Fri, 10/16): Time In = 08:00 AM | Time Out = 03:45 PM | Break = 0:30 | Daily Dec = =((D6-C6)-E6)*24 -> 7.25
Row 7 (Sat, 10/17): Off
Row 8 (Sun, 10/18): Off
--------------------------------------------------------------------------------------------------
Row 9 (Weekly Total): Formula = =SUM(F2:F8) -> Output: 39.50 decimal hours
Row 10 (Daily Average): Formula = =AVERAGE(F2:F6) -> Output: 7.90 decimal hours per workday
If using standard time formatting without multiplying by 24, summing the daily duration column containing 8:00, 8:15, 8:00, 8:00, and 7:15 with =SUM(E2:E8) formatted as [h]:mm outputs 39:30 (39 hours and 30 minutes).
If verifying complex time totals or cross-referencing paper punch records manually becomes time-consuming, you can verify your weekly totals against an online Time Card Calculator before finalizing your payroll export.
Reading the Table for an Unusual Case: Overnight and Multi-Day Spans
Standard daily subtraction assumes that Time Out is numerically larger than Time In. However, when an employee works an overnight shift or a project duration that spans multiple calendar days, subtracting the clock-in time from the clock-out time on a standard time format produces negative time or backward values.
When a shift crosses midnight or extends across multiple days, record both the date and the time in the start and end cells. For example, enter 10/12/2026 10:00 PM in cell A2 and 10/13/2026 06:30 AM in cell B2. When dates are included, Excel evaluates both the whole integer representing the calendar day and the decimal fraction representing the hour.
To calculate the total hours worked across this multi-day shift, use the difference formula multiplied by 24:
=(B2 - A2) * 24
Subtracting A2 from B2 computes the exact fractional day difference. Multiplying by 24 converts the resulting elapsed interval into hours. For an entry spanning from 10:00 PM to 6:30 AM, (B2 - A2) produces 0.354167 of a day, which multiplied by 24 yields exactly 8.50 decimal hours. If unpaid lunch breaks apply, subtract the break duration accordingly. To display this multi-day duration explicitly in text containing day and time breakdowns, custom formatting such as d "days" h "hours" m "minutes" can be applied directly to the unmultiplied date difference.
When the Table Does Not Apply
While subtraction and summation formulas work reliably for standard time records, several situations cause formulas to break or output misleading figures:
- Text Formats Causing Errors: If time values are entered or pasted as text strings rather than numeric time entries, Excel cannot perform mathematical operations. Attempting to add or subtract text-formatted times will result in a
#VALUE!error or display an incorrect00:00total. - Double-Converting Decimal Hours: A frequent timesheet error occurs when custom bracket formatting
[h]:mmis applied to a column that has already been converted to decimals via* 24. Because Excel interprets a decimal number like40.00as 40 full calendar days (960 hours), applying[h]:mmto a decimal cell produces nonsensical time totals. Only apply[h]:mmto unmultiplied time subtractions. - Accidental Formula Overwrites: In shared workbooks, managers or staff entering punch times may paste values across the entire row, inadvertently replacing formulas in the hours column with static text. To safeguard formulas, unlock only the data entry cells (Time In, Time Out, Break) and apply Excel Sheet Protection under the Review tab so formula cells cannot be edited. When importing time records from external hardware, always use Paste Special Values to prevent overwriting calculation columns.
- Salaried Exempt Staff: Weekly shift math is designed for hourly, non-exempt staff whose compensation depends on exact punch intervals. For salaried employees who do not log daily punch times, a granular daily subtraction grid does not apply, though basic project hours or paid time off tracking may still use weekly summary totals.