For an Excel timesheet with start time in B2, end time in C2, and an unpaid break duration in D2, enter =MOD(C2-B2,1)-D2 in E2 to calculate net time worked, including a shift that crosses midnight. Format E2 as [h]:mm to show elapsed hours correctly; multiply the result by 24 if you need decimal hours for a numeric total.
A practical timesheet layout
A simple daily timesheet can use one row per shift. Put the work date in column A, the start time in B, the end time in C, the unpaid break duration in D, and net hours in E. For example, enter 8:15 AM in B2, 4:45 PM in C2, and 0:30 in D2. Excel stores times as fractions of a day, so 0:30 represents half an hour. The formula in E2 returns 8:00: the nine-and-a-half-hour span between the start and end, minus a 30-minute break.
Enter actual Excel time values rather than text that only looks like a time. You can type entries such as 8:15 AM or 16:45, depending on your regional settings. Enter a break as a duration such as 0:30, not as a clock time such as 12:30 PM. Keep the date in its own column for readable records, even if your shifts are shorter than a day.
- Column A: shift date
- Column B: start time
- Column C: end time
- Column D: unpaid break duration
- Column E: net elapsed time
- Column F: net decimal hours, if needed
How the timesheet formula works
Ordinary subtraction, C2-B2, works when the end time is later than the start time on the same day. The MOD function makes the calculation handle a midnight crossing: it returns the remainder after dividing the difference by one full day. If a shift starts at 10:00 PM and ends at 6:00 AM, the raw difference is negative, but MOD changes it to the eight-hour overnight span. Subtracting D2 then removes the unpaid break.
For the layout above, with start time in B2, end time in C2, and break duration in D2, the net-duration formula is =MOD(C2-B2,1)-D2. With 10:00 PM, 6:00 AM, and a 30-minute break, it returns 7:30. The formula assumes a shift lasts less than 24 hours and that the break is entered as a valid Excel duration.
MOD treats equal start and end times as zero elapsed time, not as a full-day shift. It also cannot determine whether an entry is a mistake or an intended overnight shift. Check suspicious rows rather than assuming the formula can infer the employee’s intent.
Format results as elapsed time
Excel stores one day as the number 1, so eight hours is stored as 8/24. A standard clock-time display can wrap after 24 hours; that is a problem for weekly totals, because 40 hours may appear as 16:00. The custom format [h]:mm displays cumulative hours without wrapping, so 40 hours appears as 40:00.
In Excel for Microsoft 365 on Windows, select the result cells, press Ctrl+1 to open Format Cells, choose the Number tab, select Custom, and enter [h]:mm as the format. On a Mac, Command+1 opens Format Cells. The custom format changes how a value appears, not the underlying calculation. Avoid typing an apostrophe before a time to force it to display: that can turn the entry into text, which subtraction will not handle as a real time value.
Use a duration format for elapsed time and a clock format for entries such as a start time. A start time can display as 8:15 AM, while a total should use [h]:mm. Applying a clock format to a total may make a correct calculation look wrong once the total exceeds 24 hours.
Convert worked time to decimal hours
Some reports or payroll systems ask for hours as a decimal, such as 7.5 rather than 7:30. Since Excel time values are fractions of a day, multiply the net duration by 24. With net duration in E2, enter =24*E2 in F2. For the example shift that returns 8:00 in E2, F2 returns 8. For a 7:30 result, it returns 7.5.
Format decimal hours as a number, not as a time. Select the decimal result cells and use Home > Number Format to choose Number; set decimal places as appropriate for the report. Do not multiply a value already expressed as decimal hours by 24 again. For example, if a cell contains 7.5 decimal hours, multiplying it by 24 would produce 180, not the intended duration.
If breaks are recorded in minutes rather than as Excel durations, use a formula that converts the minutes to a fraction of an hour. With start time in B2, end time in C2, and break minutes in D2, enter =24*MOD(C2-B2,1)-D2/60. For a 9:00 AM to 5:30 PM shift with 30 entered in D2, the result is 8 decimal hours. Choose either a time-duration break entry or a minutes entry and label the column clearly; mixing the two conventions creates incorrect results.
Calculate daily and weekly totals
To total a week, sum the daily duration values rather than adding clock-in and clock-out times. If the net daily durations are in E2:E8, enter =SUM(E2:E8) in E9. Format E9 as [h]:mm. If the seven daily decimal-hour values are in F2:F8, enter =SUM(F2:F8) in F9 and format F9 as a number. For daily results of 8:00, 7:30, 8:00, 8:00, and 7:30 across five days, the weekly total is 39:00, or 39 decimal hours.
For a longer timesheet, copy the daily formula down the rows, then adjust the total range to include the intended dates. Check that the total does not include a heading or another summary row, or it may count values twice. Excel tables can make expanding ranges easier, but the basic formula works in a regular worksheet as well.
Overtime and rounding considerations
A spreadsheet can calculate hours above a threshold, but the threshold is a rule you choose to model; a formula does not establish which overtime rules apply to a particular worker. For a simple illustrative daily threshold of eight hours, if F2 contains decimal hours for one day, enter =MAX(0,F2-8) in G2 to return the amount above eight, or zero when the total is lower. If F2 is 9.5, the result is 1.5. Actual overtime calculations can depend on the work arrangement and applicable policies or laws, so confirm the required method with an appropriate qualified source.
Keep the unrounded time calculation separate from any rounding used for reporting. Rounding each punch, each shift, or the final weekly total can produce different numbers. If a documented process requires decimal values rounded to two places, and F2 contains decimal hours, use =ROUND(F2,2) in a separate reporting cell. For 7.3333 hours, the displayed rounded value is 7.33. Do not treat a chosen rounding formula as a substitute for checking the applicable timekeeping policy.
When a shift includes dates or lasts over 24 hours
The MOD formula is useful when entries contain only clock times and every shift is less than 24 hours. If the shift can last more than a day, or if you need to distinguish dates precisely, enter full date-and-time values in the start and end cells. For example, B2 might contain November 1 at 10:00 PM and C2 November 2 at 6:00 AM. With those full date-and-time values in B2 and C2 and break duration in D2, use =C2-B2-D2 in E2. It returns the elapsed duration without wrapping at midnight; format the result as [h]:mm.
Including dates also helps when a shift begins before midnight and ends after it, because the calendar date is explicit rather than inferred. If an end date is accidentally entered earlier than the start date, the result will be negative and should be investigated. A formula cannot correct a wrong date entry reliably without risking the concealment of data errors.
Common formula problems and checks
- The result shows as a decimal: the calculation may be correct, but the cell is using General or Number formatting. Apply [h]:mm for elapsed time.
- The total wraps after 24 hours: use the custom format [h]:mm rather than a clock format such as h:mm.
- The result is an error or will not calculate: check whether start, end, and break entries are real numeric time values, not text. Re-enter the values or correct the cell format and data.
- The result is negative: check for a missed overnight shift, an incorrect date, or an end time entered before the start time. MOD handles overnight shifts under 24 hours when only clock times are used.
- The result is unexpectedly short or long: confirm that the break is a duration, that the break units are consistent, and that start and end times are in the intended cells.
One useful check is to compare the net result with the elapsed span before breaks. A negative net duration usually indicates that the break is longer than the shift or that a value is incorrectly entered. For a shift crossing midnight, test the formula with a known example before filling it down. You can also use Excel’s fill handle to copy a formula, then inspect a few copied rows to confirm that the cell references changed as intended.
Frequently Asked Questions
What is the formula for hours worked in Excel?
With start time in B2, end time in C2, and an unpaid break duration in D2, use =MOD(C2-B2,1)-D2. Format the result as [h]:mm. The formula supports an overnight shift shorter than 24 hours.
How do I calculate hours worked without a break?
With the start time in B2 and end time in C2, use =MOD(C2-B2,1) for a shift under 24 hours. Format the result as [h]:mm. If B2 and C2 contain full date-and-time values and shifts can exceed 24 hours, subtract the start value from the end value instead.
How do I turn 7 hours and 30 minutes into a decimal?
With the elapsed duration in E2, use =24*E2 in another cell and format the result as a number. A duration of 7:30 becomes 7.5 decimal hours.
Why does my Excel timesheet total show the wrong number after 24 hours?
The total may be displayed with a clock-time format that restarts at midnight. Apply the custom elapsed-time format [h]:mm so a 40-hour total displays as 40:00 instead of 16:00.
Can I use the same formula for overnight shifts?
Yes, if the shift is less than 24 hours and the cells contain clock times, MOD handles the midnight crossing. For longer shifts or more explicit date tracking, enter full date-and-time values and subtract the start date-time from the end date-time.
Related
- Data Validation Testing: Methods and Examples
- Data Validation Studio: Excel Rules, Setup, and Limits
- Power Query Circular Reference: Causes and Fixes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel SUM Column Formula: Examples and Common FixesTo add numbers in a column in Excel, use the SUM function. If the values are in A2…
- VLOOKUP in Excel: Steps, Examples, and Common FixesTo use VLOOKUP in Excel, put the value you want to find in a cell, then enter a…
- Excel Filter Drop-Down Shortcut: Open a Column MenuIn Excel for Microsoft 365 on Windows, select a cell in a filtered column and press Alt+Down Arrow…
- Show Formulas in Excel: Keyboard Shortcut GuideTo show formulas in Excel for Microsoft 365 on Windows, press Ctrl+` (Ctrl plus the grave accent key,…
- Excel: How Many Days Are in a Month? Formula GuideA month can have 28, 29, 30, or 31 days, so Excel needs a year and month—or a…
- Excel LEN Function: Count Text Characters in CellsIn Excel for Microsoft 365, use the LEN function to count the characters in a cell: with text…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.