To calculate hours worked per day in Excel, enter the start time in B2, end time in C2, and unpaid break duration in D2, then use =(MOD(C2-B2,1)-D2)*24 in E2. Enter the break as an Excel time duration, such as 0:30 for 30 minutes; the result is decimal hours.
Set Up a Daily Timesheet
A simple layout makes the calculation easier to check and copy down for multiple days. Put one workday on each row, with a date and the relevant times:
- Column A: work date
- Column B: shift start time
- Column C: shift end time
- Column D: unpaid break duration
- Column E: hours worked
For example, enter 9:00 AM in B2, 5:30 PM in C2, and 0:30 in D2. Excel recognizes these as time values when the cells are entered in a recognizable time format. In E2, the formula returns 8, meaning 8.0 hours worked: the elapsed shift is 8.5 hours, less a half-hour unpaid break.
On Microsoft 365 for Windows, enter the formula in E2 and press Enter, then use the fill handle at the lower-right corner of the cell to copy it to other rows. Excel adjusts the row references automatically. On a Mac, the formula is the same; the fill handle works similarly, though keyboard and menu details can vary by version. Excel for the web also supports this formula.
How Excel Calculates Time
Excel stores time as a fraction of a 24-hour day. For instance, 12 hours is one-half of a day, and 30 minutes is 1/48 of a day. Subtracting one time from another therefore produces a fraction of a day, not a number of hours. Multiplying the difference by 24 converts it to hours.
In the daily formula, MOD(C2-B2,1) calculates the elapsed time and handles a shift that crosses midnight. Subtracting D2 removes the break, which is also stored as a fraction of a day; multiplying the net duration by 24 then produces decimal hours. The formula relies on the cells containing actual Excel time values, not text that merely looks like a time.
For a same-day shift with no break, if B2 contains the start and C2 the end, the formula is =(C2-B2)*24. A shift from 8:15 AM to 4:45 PM returns 8.5 hours. If you use the main formula with D2 left empty, Excel treats the empty break cell as zero, so it also returns the full shift length.
Subtracting Breaks Correctly
Be consistent about how break duration is entered. The main formula expects a real Excel duration such as 0:30, not the number 30 to mean 30 minutes. If you type 30 into D2, Excel interprets it as 30 days, so the result will be dramatically wrong. A useful heading such as “Unpaid break (h:mm)” helps make the expected entry clear.
There is another valid approach: enter breaks as decimal hours. With B2 as start, C2 as end, and D2 as a decimal break value—for example, 0.5 for a half-hour—use =MOD(C2-B2,1)*24-D2. For a one-hour break, enter 1. Do not mix this method with the first formula: subtracting a decimal-hour value before multiplying by 24 would subtract the wrong amount.
Only subtract breaks that should be excluded from the hours total you are calculating. Whether a particular break is paid or unpaid depends on the applicable workplace policy and rules; a spreadsheet formula does not determine that. If workers take multiple unpaid breaks, add their durations together in the break cell or use separate columns and subtract their combined duration.
Handling Shifts That Cross Midnight
Ordinary subtraction can show a negative result when a shift ends after midnight, because Excel sees the end clock time as earlier than the start clock time. MOD in the main formula wraps the difference into a 24-hour day. For example, with a 10:00 PM start in B2, a 6:00 AM end in C2, and a 0:30 break in D2, the formula returns 7.5 hours: eight elapsed hours less half an hour.
MOD is suitable for a shift lasting less than 24 hours when B2 and C2 contain clock times without dates. It treats identical start and end times as zero elapsed hours, not a full-day shift. If a shift could last 24 hours or longer, or if you record actual date-and-time values, store the full start and end date-times instead. With B2 holding the dated start, C2 the dated end, and D2 the break as an Excel time duration, use =(C2-B2-D2)*24. Because the dates distinguish the days, this calculation does not need MOD.
Format Results and Weekly Totals
The main formula returns decimal hours, which is useful for adding work time or comparing hours with a decimal threshold. Format the result cells as Number and choose the decimal places you need. For example, 8.5 means eight hours and 30 minutes; it does not mean eight hours and 50 minutes. Decimal-hour reporting is different from displaying a duration as hours and minutes.
If you prefer to display a daily duration as hours and minutes, you can calculate a time value without multiplying by 24. With B2 as start, C2 as end, and D2 as break duration, use =MOD(C2-B2,1)-D2, then apply the custom number format [h]:mm to the result cell. The square brackets let the displayed hour count exceed 24 when you add durations. In Excel for Windows, open Format Cells with Ctrl+1 and choose Custom; on Mac, Command+1 opens Format Cells. The format affects display, not the underlying value.
To add five daily decimal-hour results in E2:E6, enter =SUM(E2:E6) in E7. If the five entries are 8, 7.5, 8, 6.5, and 8, the total is 38 hours. Keep the total in decimal format if you want to see 38. If the cells contain time durations rather than decimal hours, the sum is a fraction of a day; format that total with [h]:mm to display, for example, 38:00.
Rounding and Record-Keeping Choices
Decide whether the timesheet should retain exact times or round calculated hours, and apply the same approach consistently. Rounding every day’s result can produce a different weekly total from adding the unrounded daily values and rounding only once. For example, small fractions of an hour may accumulate across several days. Keep the underlying precision if the purpose is to audit entries, and make the displayed decimal places a separate formatting choice.
If a specific calculation requires rounding each daily result to two decimal places, with B2 as start, C2 as end, and D2 as break duration, use =ROUND((MOD(C2-B2,1)-D2)*24,2). This rounds the decimal-hour result to the nearest hundredth of an hour, not to the nearest minute. A hundredth of an hour is 0.6 minutes, so this is not the same as rounding to a whole number of minutes. Use an agreed method appropriate to the timekeeping process rather than assuming a spreadsheet setting establishes a workplace rule.
For reliable records, retain the entered start, end, and break values alongside the calculated result. Protecting or clearly distinguishing formula cells can help prevent accidental edits, while data validation can help keep entries consistent. Check any validation rules against real schedules: restricting end times to be later than start times, for example, would reject a legitimate overnight shift.
Common Formula Problems
- A decimal number appears instead of hours: If the formula omits multiplication by 24, the result is a fraction of a day. Multiply the net time by 24 for decimal hours.
- The answer is unexpectedly huge or negative: Confirm that start, end, and break entries are actual time values, and that D2 is a duration such as 0:30 rather than 30.
- An overnight shift gives a negative result: Use the MOD version for clock times without dates, or record full date-and-time values and subtract those directly.
- The total displays as a time of day or wraps after 24 hours: A sum of time values may be displayed as a clock time. Apply the custom format [h]:mm to show a cumulative duration.
- The result shows ####: The column may be too narrow for the selected number format. Widen the column or choose a more suitable display format.
Also check whether the break is longer than the elapsed shift. That produces a negative net duration, which may indicate a data-entry mistake or a schedule that needs review. The formula calculates what the entries say; it cannot determine whether those entries are correct.
Frequently Asked Questions
What is the simplest Excel formula for hours between two times?
With the start time in B2 and end time in C2 on the same day, use =(C2-B2)*24. This returns decimal hours and does not subtract a break or account for an overnight shift.
How do I calculate hours worked overnight?
With start in B2, end in C2, and break duration in D2, use =(MOD(C2-B2,1)-D2)*24. This assumes the entries are clock times and the shift is shorter than 24 hours. For longer shifts, include dates in the start and end values.
Should I enter a 30-minute break as 0.5 or 0:30?
Either can work, but the matching formula differs. Use 0:30 with the formula that subtracts D2 before multiplying by 24, or use 0.5 with the formula that multiplies the shift duration by 24 before subtracting D2.
How can I show 8.5 hours as 8:30?
8.5 is a decimal-hour number, while 8:30 is a duration display. Calculate a time value without multiplying by 24, then format the cell as [h]:mm. Applying a time format directly to 8.5 decimal hours will not convert it as intended.
Can I total hours for a week?
Yes. Use SUM on the daily results. If the daily results are decimal hours, the total is decimal hours; if they are time durations, use the [h]:mm custom format so totals over 24 hours remain visible.
Related
- Excel Find the Minimum Value in a Range With Criteria
- Excel Find the Minimum Date With Criteria
- Excel Find Maximum Value Based on Criteria with MAXIFS
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Cell Value From List: Find or Select a MatchTo return a value from a list when you know a matching item, use XLOOKUP; with product IDs…
- Excel IFS Statement Examples and How to Use the FunctionExcel’s IFS function checks conditions in order and returns the result paired with the first condition that is…
- COUNTIF Examples: Formulas for Common Spreadsheet TasksUse Excel’s COUNTIF function to count cells in one range that meet one condition: =COUNTIF(range,criteria). For example, it…
- Filter Excel Horizontally: Methods That WorkYes, you can filter Excel horizontally, but the standard Filter dropdown is designed to filter records in rows,…
- Excel Group Dates by Month in a Filter: Steps & FixesTo group dates by month in an Excel filter, make sure the column contains real Excel dates, then…
- Excel Null Values: Blanks, Formulas, and Power QueryMicrosoft Excel does not have a special worksheet value called NULL in the way a database does. What…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.