Excel Calculation for Hours Worked: Formulas and Tips

Formulas & functions · Updated November 19, 2026

For a shift with start time in A2, end time in B2, and unpaid break duration in C2, enter =MOD(B2-A2,1)-C2 in D2 and format D2 as [h]:mm. This calculates same-day and overnight shifts; if your cells contain full date-and-time stamps, subtract the start from the end and then subtract the break instead.

Set Up Your Timesheet Columns

A simple timesheet needs one row per shift and separate columns for the information Excel must calculate. For example, use column A for the start time, B for the end time, C for unpaid break length, and D for hours worked. Put headers in row 1 and enter the first shift on row 2. You can add employee names, dates, job codes, or notes in other columns without changing the basic calculation.

Enter times as Excel-recognized times, such as 9:00 AM or 17:00, rather than as text like “nine o’clock.” Use a consistent convention across the sheet. Breaks should be entered as elapsed durations: a 30-minute break is 0:30, not the time of day when the break began. If breaks vary, enter each shift’s actual unpaid duration in its row.

  • A2: shift start, such as 9:00 AM.
  • B2: shift end, such as 5:30 PM.
  • C2: unpaid break, such as 0:30.
  • D2: calculated duration, formatted to show hours and minutes.

After entering the formula in D2, copy it down for the other shift rows. In Excel for Microsoft 365 on Windows, drag the fill handle at the lower-right corner of the cell, or copy and paste the formula into the remaining rows. Excel adjusts the row references automatically.

How Excel Stores Time

Excel stores dates and times as serial numbers. A whole-number portion represents days, while the fractional portion represents the time within a day. For example, 12 hours is half of a day, or 0.5. Subtracting a start time from an end time therefore produces a duration, but that duration is still stored as a fraction of a day.

This is why a calculated result may look like a clock time or a decimal rather than a count of hours. The underlying arithmetic can be right even when the displayed value is confusing. Choose a number format that matches the question you want the sheet to answer: elapsed hours and minutes, or decimal hours for a report or another calculation.

There is an important distinction between a time-only entry and a full timestamp. A time-only entry contains the clock time but not the date, so Excel cannot tell whether 6:00 AM happened on the same day or the following day unless the formula accounts for an overnight shift. Full timestamps include both date and time, so ordinary subtraction can measure a shift that crosses midnight or lasts more than one day.

Calculate Same-Day and Overnight Shifts

With start times in A2, end times in B2, and an unpaid break duration in C2, the formula =MOD(B2-A2,1)-C2 treats an end time earlier than the start time as occurring the next day. The MOD function wraps the time difference into one 24-hour day. For a 9:00 AM start, 5:30 PM finish, and 30-minute unpaid break, the result is 8:00.

The same formula works for an overnight shift. If A2 is 10:00 PM, B2 is 6:00 AM, and C2 is 0:30, the elapsed time is eight hours before the break; the result is 7:30. Without the MOD calculation, subtracting 10:00 PM from 6:00 AM would produce a negative duration, which is usually not the intended result for a shift crossing midnight.

For a shift whose start and end cells both include dates and times, with the start timestamp in A2, the end timestamp in B2, and the break duration in C2, use =B2-A2-C2. For example, a shift from November 3 at 10:00 PM to November 4 at 6:00 AM, less a 30-minute break, returns 7:30. Because the dates are part of the values, this method also handles shifts longer than 24 hours.

Time-only entries have one edge case: if start and end show the same clock time, MOD treats the elapsed time as zero, not 24 hours. If a shift can last a full day or longer, enter actual dates as well as times. Recording dates also makes a timesheet easier to audit and reduces ambiguity when shifts cross midnight.

Subtract Breaks Correctly

The break cell should represent the amount of time to deduct, not a start or finish clock reading. Enter 45 minutes as 0:45, for example. When the break is paid and should count as worked time for the purpose of your sheet, leave the break deduction at zero or use a separate column that identifies whether a break is unpaid. Which breaks are paid is a workplace-policy and jurisdiction-specific question; the spreadsheet only performs the calculation you specify.

Make sure break entries use a duration format or a clear time entry that Excel recognizes. A value typed as 30 may be interpreted as 30 days rather than 30 minutes. If the resulting duration is negative because the break exceeds the shift length, the data should be checked. Excel’s default time display does not show negative time in the usual way, so a row may appear as hash marks or another unexpected display depending on the workbook settings.

For a sheet with occasional blank rows, you can keep the formula consistent and leave the break cell blank where no deduction applies. Blank cells are treated as zero in this subtraction. If you want empty rows to remain visually empty rather than display a zero duration, use a separate blank-row display rule or a formula that checks whether the start and end entries have been filled in.

Format Durations and Convert to Decimal Hours

To display elapsed totals reliably, select the result cells, open Format Cells, choose the Number tab, then choose Custom and enter [h]:mm as the format. On Windows, Ctrl+1 opens Format Cells; on Mac, use Command+1. The square brackets tell Excel to show accumulated hours rather than restarting the hour display at 24. This is essential for weekly totals: 37 hours and 30 minutes should display as 37:30, not 13:30.

A standard time format such as h:mm is suitable for a single shift under 24 hours, but it can wrap when a summed duration exceeds one day. The custom elapsed-time format changes only how the value is displayed; it does not alter the underlying calculation. You can still add, subtract, or convert the duration afterward.

With a duration in D2, use =D2*24 in E2 to convert the result to decimal hours. If D2 contains 7:30, E2 returns 7.5. Format E2 as General or Number so Excel shows a decimal rather than a clock-style value. Decimal hours are useful for some summaries and payroll imports, but keep the original hours-and-minutes value too if you need to review the exact shift length.

Add Daily or Weekly Hours

To total several calculated shift durations, put each shift result in D2 through D4 and enter =SUM(D2:D4) in D5. If those shifts are 8:00, 7:30, and 8:00, the total is 23:30. Format D5 with [h]:mm so the total stays readable if it grows beyond 24 hours. For a longer timesheet, change the range to cover all of the relevant shift rows.

Keep the calculation at the same level as the data. If an employee works separate shifts in one day, calculate each row and sum the rows for the day. If you need weekly totals, sum the daily or shift results for that week. Adding a date column and using clear week boundaries helps avoid accidentally including a shift from the next pay period.

Excel can sum durations, but a total is not automatically a payroll result. Overtime thresholds, rounding rules, paid breaks, and the workweek definition depend on applicable rules and employer policy. A spreadsheet that simply sums hours should not be treated as confirmation that pay has been calculated correctly. Preserve the underlying punches and make any policy assumptions visible.

Common Errors and Ways to Avoid Them

  • A result displays as a decimal or a clock reading: check the cell format. Use [h]:mm for accumulated durations, or multiply by 24 and format as Number for decimal hours.
  • An overnight shift appears negative: if entries contain only times, use the MOD-based calculation. If they contain dates and times, use timestamp subtraction.
  • A weekly total appears short by 24 hours: replace a normal clock format with the elapsed-time format [h]:mm.
  • A result is unexpectedly large: check whether a break was entered as a duration, and whether the time cells contain actual Excel time values rather than text or an accidental date.
  • A blank row shows a result: the formula may be calculating zero values or a leftover break entry. Clear unused inputs, or use a formula designed to leave incomplete rows blank.

To diagnose a suspicious result, inspect the cell’s formula bar and number format, then check each input individually. A time that looks correct on screen may still be text, particularly if it was imported from another system. Re-entering it in a recognized format or converting the imported data may resolve the issue. Avoid changing the workbook’s date system just to fix one calculation; that setting affects date values across the workbook.

Choose the Right Method for Your Timesheet

For occasional shifts entered as clock times, a start, end, and break layout with a MOD formula is simple and handles shifts that cross midnight. For formal records or shifts that may span a full day, store actual dates and times and calculate from the timestamps. If you export totals to another system, check whether it expects durations such as 7:30 or decimal hours such as 7.5; the two values represent the same length of time but are not interchangeable as text.

It can also help to separate raw data from calculated results. Keep original punch times in their own columns, put formulas in result columns, and avoid typing over calculated cells. Use headers that make units explicit, such as “Break (hours:minutes)” or “Worked (decimal hours).” This reduces mistakes when someone else reads or updates the sheet.

Excel for the web supports ordinary time calculations and custom number formats, though the exact controls can differ from the Windows desktop ribbon. On a Mac, the formula syntax and basic time arithmetic are the same; keyboard shortcuts and some dialog layouts differ. If a workbook is used across platforms, test its displayed formats and formulas in the version your team relies on.

Frequently Asked Questions

What formula calculates hours worked in Excel?

With start time in A2, end time in B2, and unpaid break duration in C2, enter =MOD(B2-A2,1)-C2 in D2. Format the result as [h]:mm. The formula handles a shift crossing midnight when the cells contain times without dates.

How do I calculate an overnight shift in Excel?

For time-only entries, use the MOD-based formula described above; it treats an earlier end time as the following day. If you enter both dates and times, subtract the start timestamp from the end timestamp instead, then subtract any unpaid break.

How do I calculate hours worked minus a 30-minute lunch?

Enter the break as 0:30 in the break cell and subtract that cell in the hours-worked calculation. For a shift from 9:00 AM to 5:30 PM, this gives 8:00 after the 30-minute deduction.

How do I make Excel show more than 24 hours?

Format the total as a custom duration using [h]:mm. A normal clock format can restart after 24 hours, while square brackets keep the total elapsed hours visible.

How do I turn hours and minutes into decimal hours?

With a duration in D2, enter =D2*24 in E2 and format E2 as a number. For example, 7:30 becomes 7.5 decimal hours because 30 minutes is half an hour.

Related

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.