To calculate a shift in decimal hours, enter the start time in A2, end time in B2, and unpaid break length in C2 as decimal hours, then use =MOD(B2-A2,1)*24-C2. For example, 9:00 AM to 5:30 PM with a 0.5-hour break returns 8 working hours; the MOD function also handles a shift that ends after midnight.
How Excel Represents Working Time
Excel stores a time as a fraction of a 24-hour day: noon is 0.5, and one hour is 1/24. Subtracting a start time from an end time therefore gives a fraction of a day, not a number of hours. Multiplying the result by 24 converts it to decimal hours, which is usually the most useful form for totals, timesheets, or calculations with an hourly rate.
A cell can display a time such as 8:00 while its underlying value represents eight hours as a fraction of a day. That distinction matters when adding shifts. A normal time display can roll over after 24 hours, making a multi-day total look wrong even when the stored value is correct. Choose a display format that matches whether you want a duration or a decimal number.
- Use decimal hours, such as 8.5, when a payroll or reporting system expects a number of hours.
- Use an elapsed-time format such as [h]:mm when you want to see hours and minutes, including totals over 24 hours.
- Enter times as actual Excel times, not text that only looks like a time.
Set Up a Basic Shift Calculator
On a worksheet, put the shift start in A2, shift end in B2, and unpaid break duration in C2. Enter the break as a decimal number of hours: use 0.5 for 30 minutes, 0.75 for 45 minutes, or 1 for one hour. Format A2 and B2 as times using Home > Number, and format the result cell as Number with an appropriate number of decimal places.
With the start in A2, end in B2, and break in C2 as decimal hours, enter =MOD(B2-A2,1)*24-C2 in D2. If A2 is 9:00 AM, B2 is 5:30 PM, and C2 is 0.5, the elapsed shift is 8.5 hours and the result is 8.0 hours after the break. The result is a decimal, so 8.25 means eight hours and 15 minutes, not eight hours and 25 minutes.
MOD keeps the elapsed time positive when the end time is earlier on the clock than the start time. If the shift starts at 10:00 PM and ends at 6:00 AM, with a 0.5-hour break, the same layout and formula return 7.5 hours. That assumes the shift lasts less than 24 hours and that the two cells contain times without dates.
Subtract Breaks Entered as Times
Some timesheets record a break as an Excel duration rather than a decimal number. For example, C2 may contain 0:30, displayed as 30 minutes. Do not subtract that value as if it were 0.5 hours: Excel stores 0:30 as 1/48 of a day. Either convert it to hours in the calculation or calculate the net duration first.
With the start in A2, end in B2, and break duration in C2 as an Excel time such as 0:30, enter =(MOD(B2-A2,1)-C2)*24 to return decimal hours. For a 9:00 AM to 5:30 PM shift and a 0:30 break, the answer is 8.0. For a duration-style answer instead, with the same cell layout, use =MOD(B2-A2,1)-C2 and apply the custom number format [h]:mm to the result cell. It will display 8:00.
To apply that duration format in Excel for Microsoft 365 on Windows, select the result cells, press Ctrl+1, choose Number > Custom, type [h]:mm in the Type box, and select OK. On a Mac, select the cells and use Command+1 to open Format Cells; the custom format option is also available there. The square brackets tell Excel to show accumulated hours rather than resetting the hour count at 24.
Handle Overnight Shifts and Date Values
For shifts entered as times only, MOD is a compact way to handle a single midnight crossing. It cannot infer the intended duration if an entry is incorrect or if a shift could last longer than a day. If shifts may run beyond 24 hours, or if you need an auditable record of the actual dates, enter full date-and-time values instead of times alone.
With the complete start date and time in A2 and complete end date and time in B2, use =(B2-A2)*24 to return elapsed decimal hours. For a shift beginning at 10:00 PM on one date and ending at 6:00 AM the next date, the result is 8. If the break in C2 is a decimal number of hours, use =(B2-A2)*24-C2; with a 0.5-hour break, that example returns 7.5 hours. The date must advance in B2 for the overnight interval to calculate correctly.
Full date-and-time entries are especially helpful for shifts crossing month-end, for longer assignments, or when you need to check which calendar day an entry belongs to. Excel does not automatically account for daylight-saving changes or time zones in ordinary time subtraction. If a shift crosses a clock change, recorded local start and end times may not equal the actual elapsed time; use a reliable record of elapsed time or a method designed for the relevant time zone.
Calculate Daily and Weekly Totals
Make each row one shift, with start time in column A, end time in column B, break hours in column C, and net hours in column D. Enter the single-shift formula in D2 and fill it down for the other rows. For a weekly total, sum the calculated results rather than adding the displayed start and end times.
With daily net hours in D2:D8 as decimal numbers, enter =SUM(D2:D8) in the weekly total cell. If the shifts total 8, 7.5, 8, 8, and 6 hours, with the remaining two rows blank, the result is 37.5 hours. Format that total as Number so it remains a decimal value.
If the daily results are durations rather than decimal numbers, the sum is also a duration. With those durations in D2:D8, enter =SUM(D2:D8) and apply [h]:mm to the total cell. For 37 hours and 30 minutes, the displayed total will be 37:30. A standard time format may show 13:30 instead because the display has wrapped after 24 hours; the total itself has not necessarily been lost.
Choose Decimal Hours or Hours and Minutes
The right output depends on what the number will be used for. Decimal hours are convenient for adding, filtering, or multiplying by an hourly rate. Hours and minutes are often easier to compare with a schedule or review against a clock. Avoid interpreting the digits after a decimal point as minutes: 7.5 hours is 7 hours 30 minutes, while 7.30 hours is 7 hours 18 minutes.
For example, with net hours in D2 as a decimal number, multiply by the applicable hourly rate in E2 by entering =D2*E2 in F2. If D2 is 8 and E2 is 20, the result is 160 in the same currency units as the rate. This is only an arithmetic illustration; actual compensation can depend on workplace rules, agreements, and applicable requirements. Check the relevant terms rather than assuming a simple shift total determines pay.
Pick one representation for a calculation column and label it clearly, such as Net hours (decimal) or Net duration. Mixing decimal hours and Excel time fractions in the same column can make totals and comparisons misleading. If other people will use the workbook, include an example break entry in the header or add a note that specifies whether a break should be entered as 0.5 or 0:30.
Common Formula Problems and Checks
A result that looks like a clock time usually points to cell formatting, not necessarily a bad subtraction. Set a decimal-hours result to Number; set a duration result to [h]:mm. A result of 0.3333 days represents eight hours, so multiplying by 24 is necessary when the desired output is a decimal hour count.
- Times stored as text: If Excel treats an entry as text, arithmetic may return an error or an unexpected result. Re-enter the value in a recognized time format, or check the cell alignment and number format. Changing a text cell’s display format alone does not always convert its contents.
- Break entered in the wrong units: The basic formula expects C2 to be decimal hours. If C2 contains 0:30, use the time-duration version instead; 0.5 and 0:30 are different stored values.
- Negative or implausible results: Confirm the date and time entries, break length, and intended overnight logic. MOD assumes a shift crossing midnight when the end clock time is earlier, which may conceal an accidental entry.
- Totals that reset at 24 hours: Apply [h]:mm to duration totals, or sum decimal-hour values. Do not use a regular time-of-day format for accumulated durations.
- Rounding that changes totals: Decide whether rounding is required and at what stage. Rounding each shift before summing can produce a different total than summing exact durations and rounding once.
Rounding, Validation, and Workbook Design
Keep the raw start, end, and break entries separate from calculated results. This makes it easier to spot an incorrect time and revise the workbook without replacing source entries. Add clear column headings, consistent number formats, and a total row. For a shared timesheet, you can also use Data > Data Validation to restrict entries; the precise validation rule depends on whether breaks are decimals or time durations.
Rounding should reflect the calculation policy you actually need, not merely make a worksheet look tidy. If you want to round a decimal-hours result to two decimal places, with the start in A2, end in B2, and break in C2 as decimal hours, enter =ROUND(MOD(B2-A2,1)*24-C2,2). For an 8.1666-hour unrounded shift, the displayed rounded result is 8.17 hours. That is a display or calculation choice, not a universal rule for recording time; rounding each shift can change a weekly sum.
For reusable workbooks, test ordinary shifts, an overnight shift, a shift with no break, and a total longer than 24 hours. If blank rows should remain blank rather than show zero, add a separate blank check, but make the rule match the fields users are expected to complete. Excel for Microsoft 365 on Windows and Excel for Mac support the core time arithmetic and formats described here; Excel for the web can calculate and format these formulas too, although some dialog controls may look different.
Frequently Asked Questions
What is the simplest formula to calculate hours between two times?
With the start time in A2 and end time in B2, use =MOD(B2-A2,1)*24 for decimal hours when the shift is under 24 hours. For 9:00 AM to 5:00 PM, it returns 8. If the end time is after midnight, MOD handles that single overnight crossing.
How do I subtract a 30-minute lunch break?
With start in A2, end in B2, and break in C2 entered as decimal hours, use =MOD(B2-A2,1)*24-C2 and enter 0.5 for the break. If C2 contains the Excel time 0:30 instead, use =(MOD(B2-A2,1)-C2)*24.
How do I show a total of more than 24 hours?
If your total is an Excel duration, format its cell with [h]:mm through Format Cells > Number > Custom. The brackets prevent the hour display from restarting at zero after each 24-hour day. If the result is stored as decimal hours, use Number formatting instead.
Why does Excel show a decimal instead of hours and minutes?
Excel stores time as a fraction of a day, so a raw subtraction can produce a decimal such as 0.3333 for eight hours. Multiply that difference by 24 for decimal hours, or leave it as a duration and apply [h]:mm to display eight hours as 8:00.
Can I use the same formula for an overnight shift?
Yes, if the shift lasts less than 24 hours and the cells contain times only: MOD handles an end time earlier than the start time by treating it as the next day. For more than 24 hours or a precise dated record, enter both the start and end dates as well as times and subtract the full date-time values.
Related
- To Do List Sample Excel: Create a Practical Task Tracker
- To-Do List Template in Excel: Create and Use Yours
- To Do List Excel Sheet Template: Make a Useful Tracker
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Paste Options: Find, Choose, and Use the Right OneTo get Paste Options in Excel, copy a cell or range, select the destination, and paste with Ctrl+V;…
- Round Excel Numbers Without a Formula: Display or ChangeYes. In Excel for Microsoft 365 on Windows, use a number format to display fewer decimal places without…
- Excel COUNTIFS: How to Count Rows With OR ConditionsCOUNTIFS uses AND logic between its criteria pairs: a row must meet every condition you give it. To…
- Excel Find and Replace Within a Formula: Steps and TipsYes. In Excel for Microsoft 365 on Windows, press Ctrl+H, open Options if needed, set Look in to…
- Excel Let Text Overflow? What Happens to Cell TextYes. In Excel for Microsoft 365 on Windows, text can extend visually past a cell’s right edge when…
- Excel ROUND: Does It Round Positive or Negative Numbers?Excel’s ROUND function rounds both positive and negative numbers to the nearest value at the specified decimal place.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.