Skip to content
Owais Khan Software Reviews
How-To Guides

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.

By Owais Khan

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:

  1. 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 incorrect 00:00 total.
  2. Double-Converting Decimal Hours: A frequent timesheet error occurs when custom bracket formatting [h]:mm is applied to a column that has already been converted to decimals via * 24. Because Excel interprets a decimal number like 40.00 as 40 full calendar days (960 hours), applying [h]:mm to a decimal cell produces nonsensical time totals. Only apply [h]:mm to unmultiplied time subtractions.
  3. 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.
  4. 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.

Open the Time Card Calculator

Frequently asked questions

Why does my weekly total hours formula show 01:00 instead of 25:00 in Excel?
Excel standard clock formatting resets every 24 hours, displaying 25 hours as 1:00 AM on the following day. To fix this, change the cell format to custom [h]:mm or [hh]:mm, where the square brackets instruct Excel to display total hours over 24.
How do you convert worked hours and minutes into decimal numbers for payroll?
Subtract the start time and unpaid break from the end time and multiply the entire expression by 24, using the formula =((Time_Out - Time_In) - Break) * 24. Then format the result cell as a Number with two decimal places.
What causes a #VALUE! error when adding weekly work hours in Excel?
A #VALUE! error typically appears when one or more time entries are formatted as text rather than recognized Excel time numbers, preventing the formula from calculating the mathematical difference.
How do you calculate the average daily hours worked in a weekly schedule?
Use the formula =AVERAGE(range) across the daily hours worked cells, and format the output cell as either custom [h]:mm for hours and minutes or Number with two decimals for payroll hours.
How can you prevent employees from overwriting timesheet formulas in Excel?
Select and unlock only the time-in and time-out input cells, leave the formula cells locked, and turn on Protect Sheet under the Review tab before sharing the workbook.