For a shift that starts in A2 and ends in B2, enter =MOD(B2-A2,1)*24 to return hours as a decimal, including for a shift that passes midnight. If you want a clock-style duration instead, use =MOD(B2-A2,1) and format the result as [h]:mm.
How Excel calculates hours worked
Excel stores times as fractions of a 24-hour day. For example, a time difference of eight hours is stored as one-third of a day. Subtracting a start time from an end time therefore produces a fraction, not a number of hours. Multiplying that fraction by 24 converts it to decimal hours, which is useful for totaling time or calculating pay.
For a same-day shift, the basic calculation is simply end time minus start time. The MOD function in the opening formula makes the calculation wrap around midnight: if the end time is earlier on the clock than the start time, MOD returns the positive part of the time difference within one day. This is useful for common overnight shifts, such as 10:00 PM to 6:00 AM.
Enter actual Excel time values in the cells, such as 9:00 AM and 5:30 PM. You can type a time with AM or PM, or use 24-hour time such as 17:30. The way a time is displayed depends on its number format; the displayed appearance does not change the underlying calculation.
Calculate a single shift, with an example
For a shift beginning at 9:00 AM in A2 and ending at 5:30 PM in B2, enter =MOD(B2-A2,1)*24 in C2. The result is 8.5, meaning eight and a half hours. This is an elapsed-time total before subtracting any unpaid break.
To set up a basic timesheet:
- Put the shift start time in A2 and the shift end time in B2.
- Enter the decimal-hours formula in C2.
- Copy the formula down for other shifts by dragging the fill handle or copying and pasting it.
- Format the result cells as Number if you want a decimal such as 8.5.
Decimal hours are not hours and minutes written with a decimal point. For example, 8.5 hours is 8 hours and 30 minutes, not 8 hours and 50 minutes. Likewise, 8 hours and 15 minutes is 8.25 decimal hours because 15 minutes is one-quarter of an hour.
Subtract unpaid breaks
If C2 contains the break duration as an Excel time value, and A2 and B2 contain the shift start and end times, enter =(MOD(B2-A2,1)-C2)*24 in D2 to calculate decimal hours after the break. For a shift from 9:00 AM to 5:30 PM with a 30-minute break entered in C2 as 0:30, the result is 8. This assumes the break is to be excluded from the hours total; record paid breaks separately if you need to report them.
For a clock-style result instead, use the same cell layout and enter =MOD(B2-A2,1)-C2 in D2. Format D2 as [h]:mm. The example returns 8:00. Keeping a break duration in its own column makes the calculation easier to audit and lets you change the break without editing the formula.
Make sure the break cell contains a duration, not a time of day. A value typed as 0:30 represents thirty minutes; a value such as 12:30 PM represents a clock time and is not a suitable break duration. If the break is stored as a decimal number of minutes instead, convert it to a fraction of a day in the calculation rather than subtracting the raw number.
Handle overnight shifts and dates
The MOD version works for a single shift lasting less than 24 hours when the cells contain only clock times. For example, if A2 is 10:00 PM and B2 is 6:00 AM, =MOD(B2-A2,1)*24 returns 8. Without MOD, subtracting the earlier-looking end time from the later-looking start time would produce a negative result.
There is an important exception: when A2 and B2 contain full date-and-time values, use their actual difference rather than MOD. With a start date and time in A2, an end date and time in B2, and a break duration in C2, enter =(B2-A2-C2)*24 in D2 for decimal hours. This preserves shifts longer than 24 hours and correctly uses the dates. For example, a timestamp from Monday 10:00 PM to Tuesday 6:00 AM spans eight hours before breaks.
Do not use MOD to repair incorrectly entered dates. If a shift ends the next day, either enter the next date with the end time or use the clock-time method for a shift known to be under 24 hours. For a timesheet covering multiple days or unusually long shifts, recording both date and time is clearer and less prone to mistakes.
Choose decimal hours or hours and minutes
Decimal hours are convenient when you need to multiply hours by an hourly rate or add several shifts. For example, a result of 7.75 means seven hours and 45 minutes. Hours and minutes, such as 7:45, are often easier for people to review as a timesheet entry. Both formats can represent the same duration, but they are displayed differently.
- Use decimal hours when a downstream calculation expects a number, such as hours multiplied by a rate.
- Use a duration format when readers need to see hours and minutes directly.
- Keep the unrounded calculated value for totals where possible, then apply a rounding policy only when it is actually required.
To display a duration, select the result cells, open Format Cells with Ctrl+1 on Windows, choose Custom, and enter [h]:mm as the format. On Excel for Mac, Command+1 opens Format Cells. The square brackets are important: ordinary hour formats can restart at zero after 24 hours, while [h]:mm shows cumulative hours, such as 27:30. This changes only the display; the stored value remains a fraction of a day.
Total daily and weekly hours
If each shift’s decimal-hour result is in D2 through D6, enter =SUM(D2:D6) in D7 to add those five values. For example, daily results of 8, 7.5, 8, 6.5, and 8 total 38 hours. The SUM function is preferable to typing a chain of additions because the range is easier to extend and check.
If the daily results are durations rather than decimal numbers, SUM still adds them. Format the total cell as [h]:mm so a weekly total above 24 hours does not appear to reset at a full day. A result of 38:00 is the duration equivalent of 38 decimal hours.
For a larger timesheet, use one row per shift and keep consistent columns for date, start, end, break, and calculated hours. Then weekly totals can be made by summing the rows for that week. If the sheet includes different workers or pay periods, organize the source rows so each total includes only the intended person and date range; a simple SUM across every row would include all entries in its selected range.
Common errors and ways to prevent them
- Seeing a decimal such as 0.354 instead of hours: the cell may be showing Excel’s fraction-of-a-day value. Multiply the time difference by 24 for decimal hours, or use a duration format for hours and minutes.
- Seeing a negative result: the end time may be earlier than the start time. Use MOD for a clock-only overnight shift, or enter correct dates and subtract the full date-time values.
- Seeing a number such as 8.5 when you expected 8:30: the formula returns decimal hours. Format or calculate the result as a duration if you want hours and minutes.
- A total appears to reset after 24 hours: use the custom duration format
[h]:mmon the total cell. - The formula does not calculate as expected: check that the inputs are valid Excel times, not text that merely looks like a time. Re-entering the value or applying a time format can help reveal the issue, but formatting text alone does not always convert it into a time value.
- Breaks make the total negative: check that the break is a duration in the same units Excel expects and is not longer than the shift.
Also check whether a copied formula references the intended row. Excel normally adjusts relative references when you fill a formula down, so a formula in row 3 should use that row’s start, end, and break cells. For payroll records, retain clear source entries and document any rounding method; a formula can calculate elapsed time, but it does not decide what a workplace’s timekeeping rules require.
Rounding and pay calculations
Excel can calculate time to the precision represented by its inputs, but a displayed number may be rounded by its number format. A cell displaying 8.50 can hold more decimal places than are visible. Avoid rounding every shift unless your process specifically calls for it, because rounding each entry can produce a different weekly total than adding the precise durations and rounding once.
If D2 contains decimal hours worked and E2 contains an hourly rate, enter =D2*E2 in F2 to calculate an illustrative gross amount for that row. With 8 hours in D2 and a rate of 20 in E2, the result is 160. This is a basic multiplication example, not a determination of wages, overtime, deductions, or required rounding; check the applicable terms and rules with the employer or a qualified professional.
Frequently Asked Questions
What is the simplest Excel formula for hours worked?
For a shift under 24 hours with its start time in A2 and end time in B2, use =MOD(B2-A2,1)*24. It returns decimal hours and also handles a clock-only shift crossing midnight.
How do I calculate hours worked minus lunch in Excel?
With start time in A2, end time in B2, and lunch duration in C2, use =(MOD(B2-A2,1)-C2)*24 for decimal hours. Enter the lunch as an Excel duration, such as 0:30 for thirty minutes.
How do I show hours and minutes instead of a decimal?
Use =MOD(B2-A2,1) for a clock-only shift, then format the result cell with the custom format [h]:mm. The format shows a duration such as 8:30 instead of the decimal value 8.5.
How do I total more than 24 hours?
Sum the duration cells with SUM and format the total as [h]:mm. The square brackets allow the displayed hour count to continue past 24 instead of starting over.
Should I use MOD if my cells include dates?
Usually not. If the start and end cells contain complete date-and-time values, subtract the start from the end directly, then multiply by 24 for decimal hours. That keeps the date information and works for durations longer than one day.