Subtract Time in Excel to Get Hours as Decimal Hours

How-to basics · Updated November 20, 2026

To subtract an end time from a start time in Excel, put the start time in A2 and the end time in B2, then enter =B2-A2 in C2. Format C2 as [h]:mm to show elapsed hours and minutes, or use =(B2-A2)*24 to return decimal hours. The examples below use Microsoft Excel for Microsoft 365 on Windows and explain how to handle overnight shifts, formatting, and common errors.

What You’ll Need

  • An Excel worksheet with a start-time column and an end-time column. For the examples, start times are in column A and end times are in column B.
  • Times entered as real Excel time values, such as 9:15 AM and 5:45 PM, rather than text that only looks like a time.
  • A result column for the elapsed time or decimal-hour calculation. If you are tracking multiple shifts, leave room to fill the formula down the column.

Step-by-Step

  1. Set up the columns. In A1, enter Start Time; in B1, enter End Time; and in C1, enter Elapsed Time. Put the start and end values on the same row. For example, enter 9:15 AM in A2 and 5:45 PM in B2. You can type times with AM or PM, or use 24-hour entries such as 09:15 and 17:45. Excel stores a time as a fraction of a day, so these entries need to be recognized as times, not text.
  2. Subtract the start time from the end time. With the start time in A2 and end time in B2, enter =B2-A2 in C2. This returns the elapsed time as an Excel time value. In this example, 5:45 PM minus 9:15 AM is 8 hours and 30 minutes. The order matters: subtract the start from the end, not the end from the start.
  3. Format the result to show elapsed hours and minutes. Select C2, press Ctrl+1 on Windows, choose the Number tab, select Custom, enter [h]:mm in the Type box, and select OK. Square brackets around h tell Excel to display total elapsed hours rather than restart the hour count at 24. For this example, the cell displays 8:30. On a Mac, use Command+1 to open Format Cells. In Excel for the web, select the result cell and use Home > Number Format > More Number Formats; choose a custom format if it is available in your version.
  4. Use decimal hours when you need a number for calculations. A time display such as 8:30 means eight and a half hours, but Excel stores it as a fraction of one day. With the start in A2 and end in B2, enter =(B2-A2)*24 in another result cell. The example returns 8.5, because 30 minutes is half an hour. Format this result as Number, not Time, and choose the number of decimal places you need. The result is useful for totals, reports, or multiplying hours by an hourly rate.
  5. Copy the calculation down for more rows. If each row contains one shift, select the cell with the formula and drag its fill handle down, or copy and paste it into the cells below. With start times in A2:A10 and end times in B2:B10, the relative references adjust automatically: the next row uses B3-A3, then B4-A4, and so on. Check that each formula is beside the intended pair of times. Apply the same number format to the entire result range.
  6. Handle a shift that crosses midnight. If the cells contain time of day only, an end time such as 6:00 AM is earlier than a start time such as 10:00 PM. A direct subtraction can therefore show a row of number signs or a negative-time problem. With the start time in A2 and end time in B2, use =MOD(B2-A2,1) for elapsed time. For a 10:00 PM start and 6:00 AM finish, it returns 8 hours; format the result as [h]:mm to display 8:00. This approach assumes the shift lasts less than 24 hours and the cells contain time values without dates.
  7. Convert an overnight result to decimal hours if needed. With time-only start and end values in A2 and B2, enter =MOD(B2-A2,1)*24. For the 10:00 PM to 6:00 AM example, the answer is 8. If the shift runs from 10:00 PM to 6:30 AM, the answer is 8.5. Format the result as Number. If your data includes actual dates as well as times, use the date-and-time approach in the Tips section instead of MOD.
  8. Calculate a total only after checking the result format. If elapsed durations are in C2:C6, enter =SUM(C2:C6) in a total cell. For example, durations of 8:30, 7:45, 8:00, 6:30, and 8:15 sum to 39 hours. Format the total as [h]:mm; otherwise a total beyond 24 hours may appear to wrap around. If the cells contain decimal hours instead, SUM returns a decimal total, such as 39, and the total should be formatted as Number.

Use the display that suits the task. The [h]:mm format is easy for people to read as a duration, while decimal hours are often easier to use in further arithmetic. Changing a cell’s format changes how the stored value appears; it does not change the underlying elapsed time.

Common Mistakes

  • Using a clock-time format for a long duration. A normal format such as h:mm can reset after 24 hours. If your total is 27 hours, it may appear as 3:00. Use [h]:mm for elapsed time so the display shows the full 27 hours.
  • Forgetting to multiply by 24. The result of subtracting Excel times is a fraction of a day. A result of 0.5 represents 12 hours, not half an hour. Multiply the difference by 24 when you want a decimal-hour number, and format the cell as Number.
  • Reversing the subtraction. The end time goes first and the start time second: end minus start. Reversing the references can return a negative result. If the shift crosses midnight, use the overnight method for time-only values rather than swapping the order.
  • Entering times as text. A pasted value with a leading apostrophe, an unexpected space, or an unsupported format may look like a time but behave as text. Excel may return #VALUE! or fail to calculate as expected. Re-enter the value in a recognized time format or convert the source data before calculating.
  • Confusing an hourly decimal with hours and minutes. Decimal 8.5 means 8 hours and 30 minutes; it does not mean 8 hours and 50 minutes. The digits after the decimal represent a fraction of an hour. For instance, 0.25 hour is 15 minutes.
  • Using a time-only overnight formula on dated records. MOD is suitable for a time-only shift that crosses midnight and lasts less than a day. If A2 and B2 already contain dates and times, the dates supply the day difference. Applying MOD can discard that information, especially for a duration longer than 24 hours.

Tips

  • Include dates when shifts may span multiple days. Enter a full start date and time in A2 and a full end date and time in B2. With those date-and-time values in A2 and B2, use =B2-A2. For a start of November 3 at 10:00 PM and an end of November 4 at 6:30 AM, the result is 8 hours and 30 minutes. Format it as [h]:mm. For decimal hours, use =(B2-A2)*24, which returns 8.5 for that example. The dates make a direct subtraction work across midnight and across longer spans.
  • Keep the data consistent. Use one convention for all entries, such as AM/PM or 24-hour time, and keep each start paired with its corresponding end on the same row. A clear layout makes it easier to check formulas and spot an end time entered on the wrong date.
  • Choose a suitable decimal precision. A value such as 8.333333 may be mathematically correct for 8 hours and 20 minutes. Set the display to two decimal places if that level of precision is appropriate for your report. Number formatting rounds what is displayed, not the value Excel uses in later calculations.
  • Use a separate column for each kind of result. Keeping elapsed time in one column and decimal hours in another helps prevent confusion between a time-formatted duration and a regular number. Label the columns clearly, for example Elapsed (h:mm) and Hours (decimal).
  • Remember that breaks are not deducted automatically. If an employee takes an unpaid break, the basic end-minus-start calculation includes that time. Record the break separately and subtract it according to your tracking rules. For an eight-hour shift with a 30-minute unpaid break, the paid duration is 7 hours and 30 minutes, or 7.5 decimal hours.

Troubleshooting

The cell shows ######. The column may be too narrow, or the result may be a negative time under the current workbook settings. Widen the column first. If the result is negative and the shift crosses midnight, check that the data is time-only and use the MOD method above, or enter full dates and times and subtract them directly. Do not use MOD to conceal an incorrect date or an unexpectedly long shift.

The result shows a small decimal such as 0.3541667. The subtraction may be correct; Excel is showing the stored fraction of a day as a general number. Format it as [h]:mm to read it as a duration, or multiply the difference by 24 and format the result as Number to get decimal hours.

The formula returns #VALUE!. Check whether either input is text instead of a recognized time. Select each input and look at the formula bar, remove stray spaces or apostrophes, and type the time again. If the data came from a report or CSV file, its displayed appearance does not guarantee that Excel imported it as a time value.

The answer is one hour off or has unexpected minutes. Inspect the original entries for AM/PM mistakes, swapped start and end times, or minutes typed into the wrong position. Confirm that the displayed result format is not hiding a decimal-hour value. For instance, 8.30 as a decimal means 8 hours and 18 minutes, not 8 hours and 30 minutes.

The total looks shorter than a day’s work. Select the total cell and apply [h]:mm. A standard clock format can show only the remainder after a full day. Also confirm that all source rows are included in the SUM range and that each row’s individual elapsed time is correct before relying on the total.

Frequently Asked Questions

How do I subtract time in Excel and show the answer in hours?
Put the start time in A2 and end time in B2, enter =B2-A2, and format the result as [h]:mm. The display shows hours and minutes, such as 8:30.

How do I get a decimal number of hours?
With the start time in A2 and the end time in B2, enter =(B2-A2)*24 and format the result as Number. A difference of 8 hours and 30 minutes returns 8.5.

How do I calculate hours for a shift that ends the next day?
For time-only entries in A2 and B2 and a shift shorter than 24 hours, use =MOD(B2-A2,1) and format the result as [h]:mm. If the cells include dates, subtract the full date-and-time values directly instead.

Why does Excel show 0.5 instead of 12:00?
Excel stores 12 hours as half a day, or 0.5. Apply a time or duration format to show 12:00, or multiply the value by 24 when you need the result as a number of hours.

Can an Excel time total be more than 24 hours?
Yes. Use the custom number format [h]:mm for the total. The square brackets keep counting elapsed hours instead of restarting at zero after a day.

Does the formula subtract lunch or other breaks?
No. Subtracting the start from the end returns the full elapsed span. To calculate working time, record breaks separately and deduct them according to the rules for your worksheet.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.