To calculate hours worked when a shift starts in A2 and ends in B2 on the same day, enter =(B2-A2)*24; for a shift that may pass midnight, use =MOD(B2-A2,1)*24. These return decimal hours, so a shift from 9:00 a.m. to 5:30 p.m. returns 8.5 hours before any unpaid break is deducted.
Why the formula multiplies by 24
Excel stores time as a fraction of a day: noon is one-half, and one hour is one twenty-fourth. Subtracting a start time from an end time therefore gives a fraction of a day, not a number of hours. Multiplying the difference by 24 converts it to decimal hours, which is useful for totaling shifts or sending hours to a payroll system.
For example, with a start time of 9:00 a.m. in A2 and an end time of 5:30 p.m. in B2, the difference is 8 hours and 30 minutes. Half an hour is 0.5 of an hour, so the formula returns 8.5. Make sure both cells contain actual Excel time values, not text that merely looks like a time. You can usually enter times with a colon, such as 9:00 AM or 17:30.
If you want to see a duration as hours and minutes instead of a decimal, do not multiply by 24. Calculate the time difference and apply a duration number format, as explained below.
Calculate a same-day shift
For one shift per row, a simple table might have the employee’s name in column A, the start time in B, the end time in C, and the calculated hours in D. Put the start and end in the same row, then copy the calculation down for other shifts.
With the start time in B2 and end time in C2, and both times on the same calendar day, enter =(C2-B2)*24 in D2. For a 7:45 a.m. start and a 4:15 p.m. finish, the elapsed time is 8.5 hours. If the shift includes an unpaid break, subtract it separately rather than counting it as work time.
To fill the formula down, select D2 and drag its fill handle (the small square at the bottom-right of the selection) through the rows you need. Excel adjusts the row references as it copies. You can also copy the cell, select the destination cells, and paste. Check a few copied rows to confirm the formula refers to the start and end times on that same row.
Handle an unpaid break
There are two common ways to record breaks. If C2 contains the break duration as an Excel time value, such as 0:30 for 30 minutes, use a start time in A2 and end time in B2, then enter =(B2-A2-C2)*24. For a 9:00 a.m.–5:30 p.m. shift with a 30-minute break, the calculation is 8.5 minus 0.5, or 8.0 paid hours.
Alternatively, record the break’s start and end times. With the shift start in A2, shift end in B2, break start in C2, and break end in D2, use =((B2-A2)-(D2-C2))*24. For an 8:00 a.m.–4:30 p.m. shift and a 12:00–12:30 p.m. break, the result is 8.0 hours. This approach calculates the break duration from its two time values.
Enter a break duration as a time, not as a plain number of minutes: 30 minutes should be entered as 0:30, not 30. If your data already stores break length as minutes, convert minutes to a fraction of a day in the calculation. With shift start in A2, shift end in B2, and break minutes in C2, use =(B2-A2-C2/1440)*24. There are 1,440 minutes in a day; a 30-minute break is 30/1,440 of a day, equivalent to 0.5 hours.
Calculate overnight shifts
A basic subtraction can produce a negative duration when a shift ends after midnight, because the end time appears earlier than the start time. If both cells contain only clock times and the shift lasts less than 24 hours, MOD can wrap the difference across midnight. With a start time in A2 and an end time in B2, enter =MOD(B2-A2,1)*24. A 10:00 p.m. start and 6:30 a.m. finish return 8.5 hours.
This version assumes the shift ends on the next day if its end clock time is earlier than its start clock time. If the start and end times are identical, it returns zero, not a 24-hour shift. For shifts that may last a full day or longer, or when you need the actual dates, enter full date-and-time values instead. With the start date and time in A2 and the end date and time in B2, use =(B2-A2)*24. For example, a start on Monday at 10:00 p.m. and an end on Tuesday at 6:30 a.m. produce 8.5.
For a break during an overnight shift, use break start and end values that include their dates too. That avoids ambiguity about which day the break belongs to. As with other time calculations, these formulas work from the date and time values entered; they do not automatically adjust for a daylight-saving clock change. If exact elapsed time across such a change matters, use reliable recorded timestamps or a timekeeping system that accounts for it.
Choose decimal hours or hours and minutes
Decimal hours are convenient for arithmetic: 7 hours 45 minutes is 7.75 hours, not 7.45. A value of 7.45 means 7 hours plus 0.45 of an hour, which is 27 minutes. This distinction matters when adding shifts or multiplying hours by an hourly rate.
For a display such as 7:45, calculate the duration as an Excel time value without multiplying by 24. With start in A2 and end in B2 on the same day, enter =B2-A2, then format the result as a duration. Select the result cells, press Ctrl+1 on Windows (Command+1 on Mac), choose Number > Custom, and enter [h]:mm in the Type box. The square brackets let the hour count continue beyond 24; a regular time format can wrap around and display a 26-hour total as 2:00.
If you need decimal hours for calculations but want a separate hours-and-minutes display, keep the numeric result and use the duration calculation in another column, or use the number format appropriate to your purpose. Avoid typing a colon-formatted duration into a cell that is expected to contain decimal hours: 8:30 and 8.5 are different representations.
Total hours across multiple shifts
Calculate each shift on its own row, then sum the results. With daily decimal-hour totals in D2:D6, enter =SUM(D2:D6) in the weekly total cell. If the shifts are 8, 7.5, 8, 6.5, and 8 hours, the result is 38. This is straightforward because the daily values are already expressed as decimal hours.
If instead each row contains a time duration, sum those durations and format the total as [h]:mm. With duration values in D2:D6, enter =SUM(D2:D6) in the total cell, then apply the custom duration format. The same SUM formula adds either kind of value, but the display and units differ: decimal-hour totals show a number such as 38.5, while duration totals can show 38:30.
Keep the input columns consistent. Mixing numbers such as 8.5 with time values such as 8:30 can lead to totals that look wrong because Excel interprets the two entries in different units. Label columns clearly—for example, “Start,” “End,” “Break (time),” and “Hours (decimal)”—and use one method throughout the sheet.
Prevent blanks and inspect common errors
When a row may not yet have both a start and end time, a formula can leave the result blank until both entries are present. With start in A2 and end in B2, enter =IF(OR(A2="",B2=""),"",MOD(B2-A2,1)*24). It returns a blank if either input cell is blank; otherwise, it calculates a shift that may cross midnight. For a same-day-only sheet, replace the MOD calculation with ordinary subtraction.
If Excel shows a decimal such as 0.35417 when you expected 8.5, the formula probably subtracts times but does not multiply by 24, or the result cell is displaying a fraction of a day. If it shows a clock time instead of a decimal, change the result cell’s number format to General or Number. If you see a row of number signs, widen the column or check whether the cell’s format cannot display the value.
- Confirm start and end entries are recognized as times or date-time values, not text.
- Check that the end time is later than the start time for same-day subtraction.
- Make sure a break is entered in the units the formula expects: time, or minutes if the formula divides by 1,440.
- Review copied formulas to catch a reference pointing at the wrong row or column.
- Use a total format that can display more than 24 hours when summing durations.
Set up a reliable time worksheet
A clear layout makes the calculations easier to audit. One practical arrangement is columns for employee, work date, clock-in, clock-out, break minutes, and paid hours. Add headings with the units included, and use a separate row for each shift. If a person has two work periods in one day, record them on separate rows rather than trying to combine multiple start and end times in one cell.
For a table with date in A2, start time in B2, end time in C2, and break minutes in D2, a reliable approach is to combine each clock time with its date before subtracting when shifts can cross midnight. For example, the start date-time is built from A2 and B2 using =A2+B2; the end date-time needs the correct end date in its own cell if it falls on the following day. This is safer than guessing the date for complex or multi-day shifts. Once start and end are full date-time values and break minutes are in D2, the net hours calculation is =(EndDateTimeCell-StartDateTimeCell-D2/1440)*24. Replace the descriptive cell names with actual cell references in the worksheet; Excel formulas require cell references, not those labels.
For a simple overnight shift under 24 hours, MOD is less setup. For records that must distinguish the date, span multiple days, or be checked later, store the actual work date and timestamps. A formula can calculate elapsed time from supplied entries, but it cannot determine whether a break is paid, whether a shift is approved, or which rounding policy applies. Those depend on the employer’s timekeeping rules and applicable requirements, so use the rules that govern the records rather than assuming a spreadsheet formula defines them.
Frequently Asked Questions
What is the formula for hours worked in Excel?
For a same-day shift, subtract the start time from the end time and multiply by 24. With start in A2 and end in B2, use =(B2-A2)*24. Use MOD for a clock-time-only shift that crosses midnight.
How do I calculate hours worked minus lunch?
Enter the lunch duration as an Excel time in C2, with start in A2 and end in B2, then use =(B2-A2-C2)*24. Enter a 30-minute lunch as 0:30. If lunch is stored as minutes, subtract the minutes divided by 1,440 before converting the result to hours.
How do I calculate a shift from 10 p.m. to 6 a.m.?
With the start clock time in A2 and end clock time in B2, use =MOD(B2-A2,1)*24. For exactly 10:00 p.m. to 6:00 a.m., the result is 8 hours. This assumes the shift lasts less than 24 hours and ends the next day.
Why does my total show 2:00 instead of 26:00?
The total is likely using a clock-time format that wraps after 24 hours. Apply the custom duration format [h]:mm to the total cell so elapsed hours continue accumulating instead of restarting at zero.
Should I use 8.5 or 8:30 for eight and a half hours?
Use 8.5 when you need decimal hours for arithmetic or a decimal-hours report. Use 8:30 when you want an hours-and-minutes duration display. They represent the same duration only when stored and formatted appropriately; do not treat the digits after a decimal point as minutes.
Related
- Excel: Find Names in Two Worksheets
- Excel Find the Minimum Value in a Range With Criteria
- Excel Find the Minimum Date With Criteria
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Text Join in Excel With a Comma: Formula and ExamplesTo join the values in cells A2:A10 with a comma and a space, use =TEXTJOIN(", ",TRUE,A2:A10). The formula…
- How to Drag a Number Sequence in Excel Without GapsTo drag a number sequence in Excel, enter the first two numbers in adjacent cells, select both cells,…
- Excel Filter for Duplicate Values: Find and Review ThemTo filter duplicate values in Excel for Microsoft 365 on Windows, add a helper column with =COUNTIF($A$2:$A$100,A2)>1, fill…
- Power Query XLOOKUP: Matching Data in Excel ExplainedPower Query does not have an XLOOKUP function. To match rows from two tables in Power Query, use…
- Excel Formula for Checking Odd Even IntegersThe quickest way to check if a number in cell A1 is odd or even in Excel is…
- Excel Conditional Formatting If Not a Formula?Excel conditional formatting does not require a formula; you can apply formatting based on cell values, text, dates,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.