Excel Timesheet: Calculate Hours Worked With Formulas

To calculate a shift in an Excel timesheet, put the start time in C2, end time in D2, and unpaid break duration in E2, then enter =MOD(D2-C2,1)-E2 in F2. For a shift from 8:00 AM to 4:30 PM with a 30-minute unpaid break, the result is 8:00 hours; format the result as [h]:mm so totals display correctly.

Set up a practical timesheet layout

A clear layout makes the formulas easier to enter, copy, and check. One row per employee per workday is a useful starting point. Put labels in row 1 and enter each shift on its own row beneath them.

A: Employee name or identifier
B: Work date
C: Start time
D: End time
E: Unpaid break duration
F: Hours worked

Enter times as actual Excel time values, such as 8:00 AM or 4:30 PM, rather than text that merely looks like a time. Enter a 30-minute break as 0:30, not 30; Excel interprets 30 as 30 days in a time calculation. You can enter a duration as hours and minutes, then apply the same [h]:mm number format used for worked hours.

For a small timesheet, you can type the formula into F2 and copy it down using the fill handle at the cell’s lower-right corner. Keep the inputs consistent: use the same time convention throughout, and make it clear whether breaks are unpaid and therefore subtracted. If you use a different column arrangement, change the cell references to match it.

Calculate a same-day shift

With start time in C2, end time in D2, unpaid break duration in E2, and the result in F2, enter =MOD(D2-C2,1)-E2. For 8:00 AM to 4:30 PM and a 0:30 break, the result is 8:00. Copy the formula down for other rows, adjusting the row references automatically.

Excel stores time as a fraction of a 24-hour day. Subtracting a start time from an end time gives the elapsed portion of the day, and subtracting the break gives the time actually worked. The MOD function keeps the elapsed time within a 24-hour cycle, which also helps with overnight shifts. It does not, by itself, identify a shift lasting more than 24 hours; see the date-and-time approach below for that situation.

If a row may be incomplete while someone is filling in the sheet, with the same C2, D2, E2 and F2 layout, use =IF(OR(C2="",D2=""),"",MOD(D2-C2,1)-E2). With either time missing, the result stays blank; once the example times 8:00 AM and 4:30 PM and a 0:30 break are entered, it returns 8:00. This avoids showing a misleading zero for an unfinished shift. A blank break cell is treated as zero in this calculation.

Handle overnight shifts and full date-times

A simple subtraction such as end minus start can produce a negative value when a shift crosses midnight. For a shift from 10:00 PM to 6:00 AM, with the start in C2, end in D2, break in E2, and result in F2, =MOD(D2-C2,1)-E2 returns 7:30 when E2 contains 0:30. The calculation treats the end time as occurring on the following day.

That approach assumes the shift is shorter than 24 hours. It can be wrong if the end time is on a different date for some other reason, or if a shift lasts 24 hours or longer. In those cases, enter full date-and-time values in C2 and D2, such as 11/2/2026 10:00 PM and 11/4/2026 6:00 AM. With those date-times, the break duration in E2, and the result in F2, use =D2-C2-E2. For that example and a 0:30 break, the result is 31:30. The dates remove the ambiguity about which day the end time belongs to.

Use consistent date and time values across the workbook. A date entered as text may not behave like a true Excel date, even if it looks correctly formatted. If a calculation gives an unexpected result, select the input cell and check whether Excel recognizes it as a number or date-time rather than text.

Subtract breaks and choose what counts as work

The break column should contain only time that your timesheet rules say to exclude. If an employee takes a 30-minute unpaid meal break, enter 0:30. If a break is paid and should count toward working time, do not subtract it. The formula cannot determine whether a break is paid, legally required, or recorded correctly; those decisions depend on workplace policy and applicable rules.

For multiple unpaid breaks, add their durations in E2 before calculating the shift, or use separate break columns and subtract each one in the hours formula. For example, a 15-minute break and a 30-minute break total 0:45. Check that the break total does not exceed the shift length. A negative result is a sign to review the inputs or the timekeeping rule, not a value to silently accept.

Format hours so totals remain accurate

Excel may display a duration as a clock time or reset a total after 24 hours if the cells use an ordinary time format. For individual shifts and cumulative hours, use the custom format [h]:mm. The square brackets tell Excel to keep accumulating hours instead of wrapping the display back to zero after a full day.

In Excel for Microsoft 365 on Windows, select the relevant cells and press Ctrl+1 to open Format Cells. Choose Number, then Custom, enter [h]:mm in the Type box, and confirm. On Mac, use Command+1 to open Format Cells. In Excel for the web, select the cells and use the number-format controls on the Home tab; available custom-format options can differ from the desktop dialog. Formatting changes how a value appears, not the underlying calculation.

A duration of 8:30 means eight hours and 30 minutes. It is not the same number as 8.30 decimal hours: 30 minutes is half an hour, so 8:30 equals 8.5 decimal hours. This distinction matters when exporting totals to payroll or entering hours into a system that expects decimal values.

Add daily hours into weekly totals

Suppose F2:F6 contains five daily durations for one employee. In F7, enter =SUM(F2:F6). If the daily results are 8:00, 7:30, 8:30, 8:00, and 6:00, the total is 38:00. Format F7 as [h]:mm so it displays all 38 hours.

To create a weekly total, keep each employee’s shifts together or use a separate summary area. Before summing, check that the selected range includes the intended days and excludes headings or another employee’s entries. If you use a table with many employees and dates, filters can help you review the relevant records, but a filtered view does not automatically change what a basic SUM includes. For more structured reporting, a PivotTable can summarize recorded hours by employee or date; confirm that its source range includes all current rows.

To express a duration as a decimal number, with F2 holding an elapsed-time value such as 8:00 and G2 designated for decimal hours, enter =F2*24. The example returns 8. For 8:30, it returns 8.5. Format the decimal result as Number rather than a time format. Multiplication by 24 converts Excel’s fraction-of-a-day value into hours.

Calculate overtime separately

Overtime is a policy or rules question as well as a spreadsheet calculation. A weekly total by itself does not determine which hours qualify: thresholds, workweek definitions, daily rules, and other requirements can vary. Treat the threshold below as an example, not as a statement of what any particular worker is owed.

Suppose G2 contains total weekly hours as a decimal number, such as 45, and the example threshold is 40 hours. In H2, enter =MAX(0,G2-40). The result for 45 is 5 overtime hours, while a total of 38 returns 0. If G2 instead contains an Excel duration such as 45:00, convert it to decimal hours first or use a duration-based calculation consistently; mixing decimal numbers and time fractions produces incorrect results.

Keep regular hours, overtime hours, and total hours in clearly labeled columns if you need to report them separately. Do not assume that formatting a number as a time applies an overtime rule, or that one threshold works for every schedule. Confirm the relevant policy and requirements before using a spreadsheet total for payroll.

Common formula and timesheet problems

A total displays as a time of day: Apply [h]:mm to the duration total. A total above 24 hours may otherwise appear to wrap.
A result shows as a decimal fraction: The cell may be formatted as General or Number. Use [h]:mm for elapsed time, or multiply by 24 when decimal hours are required.
An overnight shift is negative: Use the MOD formula for a shift under 24 hours, or enter full date-times and subtract them when the dates matter.
The answer is unexpectedly large or small: Check whether a break was entered as 0:30 rather than 30, and whether the input values are genuine Excel times.
A total is missing a day: Review the SUM range and confirm that new rows are included.
A formula displays as text: Check that the cell is not formatted as Text and that the entry begins with an equals sign. Re-entering the formula after changing the format may be necessary.

It is also useful to test a timesheet with a few known examples before relying on it: a straightforward daytime shift, an overnight shift, and a row with no break. Compare the displayed result with the arithmetic in hours and minutes. Protecting formula cells from accidental edits and visually distinguishing input cells from calculated cells can reduce mistakes, though those precautions do not replace checking the source entries.

When a spreadsheet is enough—and when it is not

Excel works well for a simple personal log or a small team that needs a transparent record and has someone responsible for checking entries. It is flexible: columns, summaries, and formulas can be adapted without requiring a specialized system. A shared workbook can also make review easier, provided access, versions, and edits are managed appropriately.

A spreadsheet becomes harder to govern as the number of employees, locations, schedules, approvals, or payroll integrations grows. Formulas can be overwritten, duplicate copies can diverge, and a person reviewing a workbook may not know whether a row is complete. Dedicated time-tracking or payroll software may offer permissions, approval steps, audit records, and direct integrations, but features and costs depend on the product. Choose based on the process you need to support, not just the arithmetic. Whichever tool you use, retain a clear record of time entries and corrections according to your organization’s requirements.

Frequently Asked Questions

How do I calculate hours worked in Excel?
Subtract the start time from the end time and subtract any unpaid break. For a start in C2, end in D2, break in E2, and result in F2, use =MOD(D2-C2,1)-E2 for a shift under 24 hours, then format the result as [h]:mm.

How do I calculate a shift that ends after midnight?
For a shift shorter than 24 hours, use the MOD-based calculation; it treats an earlier end time as the next day. If the shift may last 24 hours or more, enter the full start and end date-times and subtract those values instead.

How do I convert 8 hours and 30 minutes to decimal hours?
Multiply the Excel duration by 24. The result is 8.5 decimal hours because 30 minutes is half an hour, not 0.30 hours.

Why does my timesheet total reset after 24 hours?
The total cell is probably using a clock-style time format. Apply the custom duration format [h]:mm to display cumulative hours without wrapping at 24.

Should paid breaks be subtracted from hours worked?
Subtract only breaks your timekeeping method defines as unpaid or otherwise excluded. Whether a break is paid depends on the applicable policy and rules; Excel cannot determine that from the times alone.

Can Excel calculate overtime automatically?
Excel can calculate hours above a threshold you specify, but it cannot decide which overtime rules apply. Confirm the correct workweek, threshold, and other relevant requirements before using a formula for payroll.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.