Daily Timesheet Excel Formula: Calculate Hours Correctly

Formulas & functions · Updated November 22, 2026

For a daily timesheet with a start time in B2, an end time in C2, and an unpaid break duration in D2, enter =MOD(C2-B2,1)-D2 in E2. Format E2 as [h]:mm to display the hours and minutes worked, including shifts that cross midnight.

The basic daily timesheet formula

Excel stores times as fractions of a day: noon is one-half, for example. Subtracting a start time from an end time gives the elapsed time, and subtracting the break gives the payable working duration. The MOD function makes the calculation work when the shift ends after midnight: it returns the positive time difference within a 24-hour period.

Assuming B2 contains 8:30 AM, C2 contains 5:00 PM, and D2 contains 0:30 for an unpaid half-hour break, =MOD(C2-B2,1)-D2 returns 8:00. That means eight hours worked. The result is stored as a time value, not as the number 8, so its display depends on the cell format.

Enter the formula in the first row of the hours column, then copy it to the other workdays. In Excel for Microsoft 365 on Windows, select the formula cell and drag the small fill handle at its lower-right corner down the column, or copy and paste it. Relative references adjust by row, so the next row uses B3, C3, and D3.

Set up the worksheet columns

A straightforward daily sheet separates the information people enter from the calculated result. One useful arrangement is:

  • A: Work date
  • B: Start time
  • C: End time
  • D: Unpaid break duration
  • E: Hours worked, calculated by the formula

Put headings in row 1 and enter the first workday on row 2. Enter times as recognizable time values, such as 8:30 AM or 5:00 PM. Enter a half-hour break as 0:30, not as the number 0.5: in Excel, a numeric 0.5 represents half a day, or 12 hours. For an unpaid 45-minute break, enter 0:45.

Keep the break column consistent. If one row uses a break duration and another uses a break start and end time, the same formula will not calculate both correctly. If breaks vary, record the actual duration for each shift. A blank break cell is treated as zero in subtraction, which is convenient when no unpaid break applies, but it is better to make the sheet’s rule clear to anyone entering data.

Show hours as a decimal number

Some payroll, billing, or reporting systems ask for hours as a decimal, such as 8.25 rather than 8:15. With start time in B2, end time in C2, and a break duration in D2 entered as a time such as 0:30, use =(MOD(C2-B2,1)-D2)*24. With the example shift from 8:30 AM to 5:00 PM and a 0:30 break, the formula returns 8. The multiplication by 24 converts Excel’s fraction-of-a-day result into hours.

For a shift that lasts 8 hours and 15 minutes, the decimal result is 8.25 because 15 minutes is one-quarter of an hour. If the result shows many decimal places, select the result cells and use Home > Number to choose Number, then set the desired decimal places. A decimal value is not interchangeable with a displayed time: 8:30 means eight hours and thirty minutes, while 8.5 means eight and a half hours.

If you prefer to enter breaks as decimal hours instead of time durations, use a formula that subtracts that unit. With B2 and C2 holding start and end times and D2 holding a decimal break in hours, use =MOD(C2-B2,1)*24-D2. For a 30-minute break, D2 must contain 0.5. Do not use this version with D2 entered as 0:30, because that cell is stored as a fraction of a day and the units would not match.

Handle overnight and date-stamped shifts

The MOD version is useful when the sheet records only clock times and each shift is shorter than 24 hours. For example, with a 10:00 PM start in B2, a 6:00 AM end in C2, and a 0:30 break in D2, =MOD(C2-B2,1)-D2 returns 7:30, or 7.5 decimal hours. Ordinary subtraction would produce a negative value because 6:00 AM is numerically earlier than 10:00 PM; MOD wraps the difference across midnight.

There is an important limitation: when only clock times are recorded, Excel cannot tell whether an end time on the following day represents an eight-hour shift or a longer interval involving a date change. For shifts that may last 24 hours or more, record complete date-and-time values in both the start and end cells. With B2 containing the full start date and time, C2 the full end date and time, and D2 an elapsed break duration, use =C2-B2-D2. For example, a start at 10:00 PM on one date and an end at 6:00 AM the next date, less a 0:30 break, gives 7:30.

When working with complete date-time values, format the result as [h]:mm rather than a clock-time format. The square brackets tell Excel to show accumulated hours instead of resetting the display after 24 hours. The date and time entries also make it clearer which calendar day a shift belongs to, which can matter when reviewing a timesheet.

Format and total the results

To display a daily result as hours and minutes, select the hours cells, open Format Cells with Ctrl+1 on Windows, choose Number > Custom, and enter [h]:mm in the Type box. In Excel for Mac, Command+1 opens Format Cells. The same custom format works in Microsoft 365 for Windows and Mac. For decimal-hour output, format the result as a number instead.

A weekly total can be calculated by adding the daily results. With daily duration values in E2:E6, enter =SUM(E2:E6) in E7. If the five daily entries are 8:00, 7:30, 8:00, 8:00, and 8:00, the sum is 39:30, meaning 39 hours and 30 minutes. Format E7 as [h]:mm; a normal h:mm format can display a total over 24 hours misleadingly as a time of day. If E2:E6 contain decimal hours instead, the same SUM formula returns 39.5.

For a larger sheet, keep the total outside the range being summed. If the total cell is included in its own SUM range, Excel may report a circular reference. You can also label the total clearly and distinguish it visually from the daily entries so that someone reviewing the sheet does not mistake it for another shift.

Check the inputs and avoid common errors

Most incorrect totals come from inconsistent inputs or display settings rather than a complicated formula. Check these points when a result looks wrong:

  • Confirm the break unit. A time-formatted duration such as 0:30 is different from the decimal number 0.5. Use a formula that matches the unit entered.
  • Check the result format. A correct duration may appear as a clock time or as a small decimal if the cell has an unsuitable format. Use [h]:mm for elapsed hours and minutes, or Number for decimal hours.
  • Check that entries are real times. Text that looks like a time may not subtract correctly. Re-enter the value in a recognized time format, or inspect the cell’s number format and contents.
  • Look for a missing date on an overnight shift. MOD handles a shift crossing midnight when the interval is under 24 hours, but complete dates are safer for longer intervals.
  • Check for a break longer than the shift. Subtracting an excessive break produces a negative duration. Review the entry rather than hiding the negative result with a display format.

For a basic worksheet, it is also useful to label whether breaks are unpaid and whether the sheet records actual elapsed time or rounded time. A formula calculates what the entered values imply; it does not decide which time should count under a workplace policy. Avoid rounding each clock-in and clock-out value unless the applicable policy specifically requires it, because repeated rounding can change the daily and weekly totals.

Choose the right calculation for the timesheet

The best formula depends on how the source data is recorded. Use the MOD formula when start and end are clock times and shifts are under 24 hours. Use full date-time entries with direct subtraction when shifts might exceed 24 hours or when you need an unambiguous record of dates. Choose decimal output when another system asks for numeric hours; otherwise, an elapsed-time display is often easier for people to read.

Also decide whether breaks are recorded as a duration or as separate start and end times. A duration column is compact and makes the basic calculation simple. Separate break times can provide a more detailed record, but the sheet then needs to calculate the break length before subtracting it from the shift. Do not mix the two approaches without clearly distinguishing the columns and formulas.

A daily timesheet is not automatically a payroll rules engine. The simple formula does not determine overtime eligibility, paid rest breaks, holiday treatment, or required rounding. Those rules depend on the relevant policy and circumstances. Treat the spreadsheet as a calculation of entered time and verify any payroll-specific requirements with the appropriate employer or qualified professional.

Frequently Asked Questions

What is the simplest Excel formula to calculate hours worked in a day?
With start time in B2, end time in C2, and unpaid break duration in D2, use =MOD(C2-B2,1)-D2. Format the result as [h]:mm to show elapsed hours and minutes.

How do I calculate hours worked without a break?
With a start time in B2 and an end time in C2, use =MOD(C2-B2,1). This returns a positive elapsed duration for a shift under 24 hours, including one that crosses midnight.

How do I calculate a timesheet total in decimal hours?
With start and end times in B2 and C2 and a time-formatted break in D2, use =(MOD(C2-B2,1)-D2)*24. For 8 hours and 15 minutes, the decimal result is 8.25.

Why does my total show a time instead of the number of hours?
Excel may be displaying the result with a clock-time format. Apply the custom format [h]:mm for accumulated hours, or choose Number if you want a decimal-hour value. Formatting changes how a value appears, not the underlying calculation.

Can the formula calculate an overnight shift?
Yes. MOD handles a shift that crosses midnight when the recorded interval is less than 24 hours. If a shift might last 24 hours or longer, enter the full date and time for both clock-in and clock-out, then subtract the two date-time values.

Alternatives to a basic daily sheet

For a small number of employees or a personal record, a simple worksheet with one row per day can be sufficient. For several workers, recurring schedules, approvals, or detailed audit trails, consider whether a structured Excel table or dedicated time-tracking system would better fit the process. A table can make it easier to extend formulas as new rows are added, while a specialized system may provide controlled entry and reporting features. Whichever approach you use, retain clear column labels, consistent time units, and a visible distinction between entered data and calculated totals.

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.