Excel Timesheet Hours Formula: Calculate Time Worked

Formulas & functions · Updated November 20, 2026

To calculate a shift in decimal hours in Excel, put the start time in B2 and end time in C2, then enter =MOD(C2-B2,1)*24 in D2. For example, a shift from 9:00 AM to 5:30 PM returns 8.5 hours; the MOD function also handles a shift that ends after midnight.

The basic timesheet formula

Excel stores times as fractions of a 24-hour day. Subtracting a start time from an end time therefore produces a fraction, not a number of hours. Multiplying by 24 converts that fraction into decimal hours, which is often convenient for totals, payroll records, or other calculations. A shift of 8 hours and 30 minutes becomes 8.5, not 8.30.

For the basic example, with a start time in B2 and an end time in C2, enter this formula in D2:

=MOD(C2-B2,1)*24

For a shift from 9:00 AM to 5:30 PM, the elapsed time is 8 hours 30 minutes, or 8.5 decimal hours. The formula can be copied down the column for additional shifts. MOD keeps a negative time difference from appearing when the end time is after midnight, as long as the shift is shorter than 24 hours.

Set up the timesheet columns

A simple layout makes the calculation easier to check. Put a date in column A, the shift start in B, the shift end in C, any unpaid break in D, and the calculated hours in E. Use one row per shift or workday, and add descriptive column headings in row 1. For example, row 2 can contain Monday’s date, a start time, an end time, a break, and the formula result.

For a calculation that subtracts an unpaid break entered as a time duration, with start in B2, end in C2, and break duration in D2, put this in E2:

=(MOD(C2-B2,1)-D2)*24

If B2 is 9:00 AM, C2 is 5:30 PM, and D2 is 0:30, the result is 8.0 hours. Enter the break as a duration such as 0:30, not as the clock time 12:30 PM. Format the break cells as a time or duration so that the value is clear. If your organization records paid breaks, do not subtract them unless your timesheet rules require it.

After entering the formula once, use the fill handle—the small square at the lower-right corner of the selected cell—to copy it down. Excel adjusts the row references automatically. Check the first and last copied rows to make sure the formula refers to the matching start, end, and break cells.

Format decimal hours and elapsed time

Choose the output format based on how people will read or use the result. Decimal hours are useful for arithmetic and reports that expect values such as 7.75. Select the result cells and choose Home > Number Format, then select Number and set the decimal places you need. Two decimal places display 7 hours 45 minutes as 7.75.

Some timesheets are easier to read as hours and minutes, such as 7:45. With start in B2, end in C2, and a break duration in D2, use this formula in E2:

=MOD(C2-B2,1)-D2

This returns an Excel time duration. Apply the custom number format [h]:mm to E2, using Home > Number Format > More Number Formats, then the Custom category. The square brackets mean elapsed hours keep accumulating past 24; without them, a 27-hour total may display as 3:00. On Windows, Ctrl+1 opens Format Cells; on Mac, Command+1 opens the equivalent dialog. The ribbon labels can differ slightly in Excel for the web.

Do not confuse a display format with the stored value. Formatting a duration as 8:30 does not turn it into the decimal number 8.5; it only changes how Excel displays the time fraction. Use the multiplied formula when another calculation or export needs decimal hours.

Handle overnight shifts

When a shift starts before midnight and finishes the next morning, ordinary subtraction can produce a negative time. MOD corrects that for a single shift under 24 hours. With the start in B2 and end in C2, the decimal-hours formula is:

=MOD(C2-B2,1)*24

For a shift from 10:00 PM to 6:00 AM, the result is 8. If a 30-minute unpaid break is in D2 as a time duration, use this version in E2 instead:

=(MOD(C2-B2,1)-D2)*24

With D2 equal to 0:30, the result is 7.5 hours. This method assumes the times alone describe a shift shorter than 24 hours. If a job can span 24 hours or more, or if shifts may have dates that need to be distinguished, record complete start and end date-times rather than relying on MOD to infer the day.

For complete date-times in B2 and C2, where each cell contains both a date and a time, use this formula in D2:

=(C2-B2)*24

For a start at 10:00 PM on one date and an end at 6:00 AM the next date, the result is 8. Enter the correct date with each time; otherwise, Excel cannot know which day the end belongs to. A duration displayed with [h]:mm can make these longer elapsed-time results easier to inspect.

Calculate daily and weekly totals

Keep each shift’s result in a consistent unit before adding it. If column E contains decimal hours, the weekly total can be a straightforward sum. With daily decimal-hour results in E2:E6, enter this in E7:

=SUM(E2:E6)

If the daily results are 8, 7.5, 8, 6.5, and 8 hours, the total is 38 hours. Format the total as Number if you want a decimal value. For an hours-and-minutes timesheet, sum the duration values instead and apply [h]:mm to the total cell; the same five shifts display as 38:00.

A common mistake is adding text entries or mixing decimal hours with time durations. For example, 7.5 decimal hours is seven and a half hours, while a time value displayed as 7:30 represents the same duration in a different form. They should not be mixed in one total without converting one type. If SUM appears to omit an entry, check whether that cell contains a number or time value rather than text.

Subtract breaks and other unpaid time

Use a dedicated break column so the entries and calculations remain visible. A break entered as a time duration can be subtracted directly from the elapsed-time value before multiplying by 24. For start in B2, end in C2, and break duration in D2, the formula in E2 is:

=(MOD(C2-B2,1)-D2)*24

For a 9:00 AM–5:30 PM shift with a 30-minute break, this returns 8.0. If break durations are instead entered as decimal hours—such as 0.5 for 30 minutes—convert them to a fraction of a day before subtracting. With start in B2, end in C2, and decimal break hours in D2, use this formula in E2:

=MOD(C2-B2,1)*24-D2

The result is also 8.0 for that example. Pick one break-entry convention and label the column clearly; entering 0.5 in a column intended for time values can be interpreted as 12 hours, not 30 minutes. Confirm which breaks count as paid or unpaid under the applicable workplace policy rather than assuming every break should be deducted.

Rounding, overtime, and reporting

Keep the unrounded time calculation when possible, then apply rounding only if the timesheet process calls for it. Displaying two decimal places is not the same as rounding the stored value: the underlying number may retain more precision. Rounding to two decimal hours can also produce fractions of an hour rather than neat minute increments. For example, 8.333 hours rounds to 8.33, which is not exactly 8 hours 20 minutes.

If a report specifically needs to round to the nearest 15 minutes, with start in B2, end in C2, and no break, use this illustrative formula in D2:

=ROUND((MOD(C2-B2,1)*1440)/15,0)*0.25

A 7-hour-and-22-minute shift is 442 minutes. The formula rounds 442 to the nearest multiple of 15 minutes, 435, and returns 7.25 decimal hours. This is an example of a spreadsheet method, not a recommendation about what rounding rule to use. Follow the required timekeeping policy and applicable rules; rounding choices can affect recorded totals.

Overtime calculations also depend on the rules used by an employer and the applicable jurisdiction. Excel can calculate hours above a threshold, but a formula does not determine which threshold, work period, or exceptions apply. For a simple illustrative daily threshold of eight hours, with daily decimal hours in E2, this formula in F2 returns hours above eight:

=MAX(0,E2-8)

If E2 is 9.5, F2 returns 1.5. Treat this only as a mathematical example; use the correct policy and seek qualified guidance where needed. A daily threshold may not match a weekly or other overtime rule.

Common formula problems and checks

  • A result looks like a clock time: The formula may return a time fraction. Multiply the duration by 24 for decimal hours, or keep the duration and use [h]:mm.
  • Excel shows hashes or an unexpected time: Widen the column and check its number format. For totals over 24 hours, use [h]:mm.
  • The answer is much too large: Check whether the break cell is a time duration or decimal hours. Subtracting a decimal such as 0.5 from an Excel time fraction treats it as half a day.
  • The formula does not calculate: Start and end entries may be stored as text. Re-enter them as recognized times, such as 8:30 AM, and check that the cells are not formatted or imported as text.
  • The overnight result is wrong: Confirm that the shift is under 24 hours if using MOD with time-only cells. For longer shifts, include actual dates and times.
  • The total is missing rows: Check the SUM range and inspect whether all daily results are numeric. Blank rows are harmless, but text-formatted numbers may not add as expected.

Use a few manually checked rows before relying on a completed timesheet. Verify a normal daytime shift, one overnight shift if relevant, and a row with a break. Also compare a weekly total with the sum of the visible daily results. These checks can reveal incorrect references, inconsistent entry formats, or a formula copied one row too far or too short.

When a spreadsheet is not enough

A basic Excel timesheet is practical for a small, consistent schedule, especially when one person maintains the workbook and can review entries. It can calculate elapsed time and totals, but it does not automatically verify that punches are accurate, interpret workplace rules, or resolve overlapping shifts. Protecting formula cells and using data validation can reduce accidental edits, but neither replaces review.

For a larger team, rotating schedules, approvals, multiple pay rules, or integration with payroll, dedicated time-tracking software may reduce manual handling. The right option depends on the required reports, access controls, and the way records need to be retained. If a workbook is shared, agree on who edits it and how corrected entries are documented. Keep an unchanged source copy or version history where appropriate, and check calculated totals before using them for formal reporting.

Frequently Asked Questions

How do I calculate hours worked in Excel?

With the start time in B2 and end time in C2, enter =MOD(C2-B2,1)*24 to return decimal hours. For 9:00 AM to 5:30 PM, the result is 8.5. Subtract an unpaid break separately if your timesheet requires it.

How do I calculate hours worked with a lunch break?

With start in B2, end in C2, and a break duration in D2 entered as a time such as 0:30, use =(MOD(C2-B2,1)-D2)*24. A 9:00 AM–5:30 PM shift with a 30-minute break returns 8.0 decimal hours.

How do I calculate a shift that ends after midnight?

For a shift under 24 hours, with start in B2 and end in C2, use =MOD(C2-B2,1)*24. For a shift that may last 24 hours or more, enter full start and end date-times and subtract the earlier date-time from the later one.

How do I show hours and minutes instead of decimal hours?

Return a duration with =MOD(C2-B2,1), using start in B2 and end in C2, then apply the custom number format [h]:mm. The square brackets display total elapsed hours rather than resetting the count at 24.

Why does Excel show 8:30 instead of 8.5?

The cell is probably formatted as a time or duration. Excel stores time as a fraction of a day, so the display format changes how that value looks. Use a formula that multiplies by 24 and format the result as Number to show decimal hours such as 8.5.

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.