Excel Formula for Tracking Hours in a Daily Timesheet

Formulas & functions · Updated November 19, 2026

For a basic Excel timesheet, put a start time in B2 and an end time in C2, then enter =(C2-B2)*24 in D2 to calculate hours as a decimal. If you need to subtract an unpaid break, enter its duration in E2 and use =(C2-B2-E2)*24 instead.

Those formulas work because Excel stores times as fractions of a day: subtracting one time from another returns a fraction, and multiplying by 24 converts that fraction to hours. The right formula depends on whether shifts cross midnight, how breaks are recorded, and whether you want a decimal total or an hours-and-minutes display.

Set up a simple timesheet

A clear column layout makes formulas easier to check and copy. For a straightforward daily log, use columns like these:

  • A: Work date
  • B: Start time
  • C: End time
  • D: Hours worked

Enter actual Excel times in B and C, such as 8:15 AM and 4:45 PM. You can type times with AM or PM, or use 24-hour notation such as 16:45. Format those cells as times using Home > Number Format, then select a time format. In D2, enter =(C2-B2)*24. With 8:15 AM in B2 and 4:45 PM in C2, the result is 8.5 hours, meaning eight hours and thirty minutes. Copy the formula down for the remaining rows.

Keep each row to one work period unless you deliberately build a more detailed layout. If a person clocks out and back in around a break, recording each work segment separately can be clearer than trying to infer unpaid time from one start and end pair.

Subtract an unpaid break

If a shift includes a break that should not count as working time, give it its own column. For example, put the break duration in E2 and the calculated hours in F2. Enter a duration such as 0:30 for a half-hour break; Excel treats that as half an hour, not as the number 30.

With the start time in B2, end time in C2, and break duration in E2, use =(C2-B2-E2)*24 in F2. If the shift runs from 8:15 AM to 4:45 PM and E2 contains 0:30, the result is 8 hours: the elapsed time is 8.5 hours and the unpaid break is 0.5 hour. Format E2 as a duration such as h:mm so the break is easy to read.

This calculation assumes that the break value is an Excel time duration. If you instead enter the break as a decimal number of hours, such as 0.5, do not subtract it as though it were a time value. With the same start and end columns and a decimal break in E2, use =(C2-B2)*24-E2. Choose one break-entry convention and label the column so people do not mix 0:30 and 0.5.

Calculate shifts that cross midnight

A normal subtraction can return a negative result when the end time is after midnight. For a shift that starts at 10:00 PM and ends at 6:00 AM, Excel sees the end time as earlier on the same day unless the date is included. If every shift is shorter than 24 hours and you record only clock times, the MOD function can account for the midnight rollover.

With the start time in B2, end time in C2, and break duration in E2, enter =MOD(C2-B2,1)*24-E2*24 in F2. If B2 is 10:00 PM, C2 is 6:00 AM, and E2 is 0:30, the result is 7.5 hours: eight elapsed hours minus a half-hour break. Here E2 is an Excel time duration, such as 0:30. MOD returns the remainder within one day, allowing the end time to wrap past midnight.

For work periods that might last 24 hours or longer, or when accurate dates matter, enter full date-and-time values instead. For instance, record the start as a date plus 10:00 PM and the end as the following date plus 6:00 AM. With the start date and time in B2, end date and time in C2, and break duration in E2, use =(C2-B2-E2)*24. This calculates the actual elapsed interval without relying on a one-day rollover assumption.

Choose between decimal hours and elapsed time

Decimal hours are useful when totals will be multiplied by an hourly rate or compared numerically. A result of 7.5 means seven and a half hours, not seven hours and fifty minutes. If you prefer the clock-style display 7:30, do not multiply the difference by 24; display the result as an elapsed duration instead.

With the start time in B2 and end time in C2, enter =C2-B2 in D2, then format D2 as a duration. On Windows, select the result cells and press Ctrl+1 to open Format Cells. On Mac, press Command+1. Choose Custom and enter [h]:mm as the format. The square brackets make total hours accumulate beyond 24 instead of starting over at zero.

Use decimal formatting for decimal-hour results. For example, a numeric result of 8.5 can be displayed with one or two decimal places through Home > Number Format. Avoid applying a time format to a decimal value: 8.5 as a number means 8.5 hours, while Excel interprets a time value of 8.5 as eight and a half days.

Total hours for a week or pay period

Once each row calculates a consistent kind of result, sum the hours column. With decimal daily totals in F2:F6, enter =SUM(F2:F6) in F7. If the five daily results are 8, 7.5, 8, 8, and 6.5, the total is 38 hours. Check that the range includes all intended workdays and does not include a header or an earlier total.

If daily results are elapsed-time values rather than decimal hours, SUM still adds them, but format the total cell as [h]:mm. For example, the same 38-hour week should display as 38:00. A standard time display such as h:mm can wrap after 24 hours and show 14:00 for a 38-hour total, which is a display issue rather than a bad sum.

For a growing log, turn the range into an Excel table using Insert > Table. Tables can carry formulas down as new rows are added and make it easier to filter by date or employee. Keep the summary total outside the rows being summed, or use a table total row carefully, so the total is not accidentally included in its own range.

Common formula and data-entry problems

Most timesheet errors come from inconsistent inputs or formatting rather than complicated arithmetic. Check these points when a result looks wrong:

  • A time is stored as text: A value pasted with an apostrophe or imported in an unfamiliar format may not be a real Excel time. Re-enter it as a time or use an appropriate conversion step before calculating.
  • The result shows a decimal fraction: A value such as 0.3541667 is a fraction of a day. Multiply by 24 for decimal hours, or format the unmultiplied result as h:mm.
  • The total appears to reset after 24 hours: Use the custom duration format [h]:mm for cumulative elapsed hours.
  • An overnight result is negative: Use MOD for time-only shifts under 24 hours, or enter both dates and times and subtract the full date-time values.
  • A break is subtracted incorrectly: Confirm whether it is entered as a time duration like 0:30 or as decimal hours like 0.5, then use the matching formula.
  • A formula returns an unexpected zero: Check for blank or incorrectly entered start and end times, and verify that the formula references the intended row.

For better review, use consistent date and time formats and retain the original clock-in, clock-out, and break entries. A calculated total is easier to audit when its source values remain visible. If rounding is needed for a particular process, decide whether to round each shift or only the final total; rounding each row can produce a different sum from rounding once at the end.

What an hours formula does not decide

An elapsed-time formula measures the difference between recorded times and subtracts whatever break duration you specify. It does not determine whether a break is paid, which rounding rules apply, how overtime is classified, or what records an employer must retain. Those policies depend on the applicable workplace rules and agreements; confirm them with an appropriate payroll or HR professional rather than assuming the spreadsheet formula sets them.

Likewise, a formula cannot tell whether a clock entry is accurate. A good workbook separates raw entries from calculated values, labels break assumptions, and makes totals reviewable. Protecting formula cells from accidental edits can also help, but worksheet protection is not a substitute for keeping a secure, backed-up record.

Frequently Asked Questions

What is the simplest Excel formula to calculate hours worked?
With the start time in B2 and end time in C2, use =(C2-B2)*24 for decimal hours. If you want an hours-and-minutes display, use =C2-B2 and format the result as [h]:mm.

How do I calculate hours in Excel and subtract lunch?
With start and end times in B2 and C2 and a break duration in E2 as an Excel time such as 0:30, use =(C2-B2-E2)*24. If the break is entered as decimal hours, subtract that number after multiplying the time difference by 24.

How do I calculate an overnight shift in Excel?
With clock times only in B2 and C2 for a shift shorter than 24 hours, use =MOD(C2-B2,1)*24. For a break duration in E2, subtract it with =MOD(C2-B2,1)*24-E2*24. Recording the actual start and end dates is preferable when available.

Why does my weekly total show the wrong hours after 24?
Excel may be displaying the total with a clock format that wraps at 24 hours. Keep the elapsed-time values and apply the custom format [h]:mm to the total cell, or calculate decimal hours and use a number format.

Should I enter 30 minutes as 0.5 or 0:30?
Either can work, but they represent different kinds of values. Use 0:30 for an Excel time duration and subtract it before multiplying by 24; use 0.5 for decimal hours and subtract it from the converted hour result.

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.