Excel treats time as a decimal number, so subtracting one time from another gives you a result you need to format as time to read

When you subtract 9:00 AM from 5:00 PM in Excel, the cell shows something like 0.667 instead of 8 hours. That decimal is correct — Excel is storing the answer as a fraction of a 24-hour day — but you need to format the cell to see it as hours and minutes. The same principle applies whether you are measuring how long a meeting lasted, how many hours someone worked, or how much time remains until a deadline.

The core formula is simple: end time minus start time. The formatting step is what most people miss, and it is what makes the answer readable.

Key Takeaways

  • Subtract the start time from the end time in the same cell: =B1-A1 where A1 holds the start time and B1 holds the end time.
  • Format the result cell as time by right-clicking, choosing Format Cells, selecting Time from the Category list, and picking a format that shows hours and minutes.
  • For durations longer than 24 hours, use the format code [h]:mm or [h]:mm:ss in the custom format box so Excel does not loop back to zero.
  • If times cross midnight (end time is earlier in the day than start time), add 1 to your formula: =(B1-A1)+1 to get the correct duration.
  • Use the TIME function to build time values from hours, minutes, and seconds: =TIME(8,30,0) creates 8:30:00 AM.

The basic subtraction formula and why formatting matters

Open a new Excel sheet and enter a start time in cell A1 — type 9:00 AM. In cell B1, enter an end time — type 5:30 PM. In cell C1, type the formula =B1-A1 and press Enter. The cell will show a decimal like 0.354167, which is Excel's internal representation of 8.5 hours as a fraction of a day.

To see this as hours and minutes, right-click cell C1 and select Format Cells. In the dialog box, click the Number tab if it is not already selected. In the Category list on the left, click Time. You will see several time format options in the middle column. Choose one that shows hours and minutes — for example, 13:30:55 format. Click OK. Now cell C1 displays 8:30, which is the correct duration.

The reason you must format is that Excel does not know whether you want to see the result as a time, a percentage, a decimal, or something else. Formatting tells Excel how to display the number you have calculated.

Handling times that cross midnight

If your end time is earlier in the day than your start time — for example, you started work at 11:00 PM and finished at 6:00 AM the next morning — a simple subtraction gives a negative result or a very large decimal. Excel interprets 6:00 AM minus 11:00 PM as going backward in time.

To fix this, add 1 to your formula: =(B1-A1)+1. The 1 represents one full day, which shifts the calculation forward. So if B1 is 6:00 AM and A1 is 11:00 PM, the formula becomes (0.25 - 0.9583) + 1 = 0.2917, which formats as 7:00 hours — the correct duration.

This works because Excel stores a full day as 1. Adding 1 tells Excel to count the overnight span as a positive duration instead of a backward jump.

Displaying durations longer than 24 hours

If you calculate a duration that spans more than one full day, standard time formatting loops back to zero. For example, if someone worked 30 hours, a normal time format shows 6:00 (the remainder after 24 hours) instead of 30:00.

To show the full duration, you need a custom format code. Right-click the cell with your duration, select Format Cells, click the Number tab, and choose Custom from the Category list. In the Type field, enter [h]:mm (with square brackets around the h). The square brackets tell Excel to count all hours, not just the hours within a 24-hour cycle. Click OK. Now a 30-hour duration displays as 30:00 instead of 6:00.

Use [h]:mm:ss if you also need to see seconds. The square brackets are essential — without them, Excel treats the format as a regular time and wraps at 24 hours.

Building time values with the TIME function

Instead of typing times directly, you can build them using the TIME function, which takes three arguments: hours, minutes, and seconds. The formula =TIME(8,30,0) creates the time value 8:30:00 AM. This is useful when you are pulling hours, minutes, and seconds from other cells or calculations.

For example, if cell A1 contains the number 8, cell B1 contains 45, and cell C1 contains 30, the formula =TIME(A1,B1,C1) produces 8:45:30. You can then subtract this from another time to calculate a duration, or use it in any other time calculation.

The TIME function always returns a time between 0:00:00 and 23:59:59. If you enter values that exceed these bounds — for example, =TIME(25,0,0) — Excel wraps around: 25 hours becomes 1:00:00 AM (one hour into the next day).

Calculating hours worked with decimal results

Sometimes you need the result as a decimal number of hours rather than as hours and minutes. For example, a payroll system might need 8.5 hours instead of 8:30. To convert a time duration to decimal hours, multiply the result by 24.

If cell C1 contains your duration (formatted as time), the formula =C1*24 in a new cell converts it to decimal hours. A duration of 8:30 becomes 8.5. Format this result cell as a number with two decimal places so it displays clearly. This approach works because Excel stores time as a fraction of a day, and multiplying by 24 converts that fraction to hours.

Common mistakes and how to avoid them

The most frequent error is forgetting to format the result cell. You calculate the duration correctly, but it displays as a decimal, and you assume the formula is wrong. Always format the result cell as time immediately after entering the formula.

Another common mistake is entering times in an inconsistent format. If one cell contains 9:00 AM and another contains 17:00 (military time), Excel may not recognize both as times. Use the same format throughout your sheet — either all 12-hour (with AM/PM) or all 24-hour — or use the TIME function to ensure consistency.

A third mistake is forgetting the square brackets when displaying durations longer than 24 hours. Without [h]:mm, a 30-hour duration shows as 6:00, which looks correct but is actually wrong. The brackets are easy to overlook but essential for accurate display.

Frequently Asked Questions

Why does my time calculation show a negative number or a very large decimal?

This usually means your end time is earlier in the day than your start time, so Excel is subtracting a larger number from a smaller one. Add 1 to your formula to account for the overnight span: =(B1-A1)+1. If the result is still wrong, check that both cells actually contain times — if one contains text that looks like a time, Excel treats it as text and the math fails.

How do I calculate time in minutes instead of hours?

Multiply your duration by 1440 (the number of minutes in a day). If C1 contains your duration, the formula =C1*1440 converts it to minutes. A duration of 2:30 becomes 150 minutes. Format the result cell as a number, not as time.

Can I add or subtract time from a specific time to get a new time?

Yes. To add 3 hours to a time in cell A1, use =A1+TIME(3,0,0). To subtract 45 minutes, use =A1-TIME(0,45,0). Format the result as time to see the new time value. This works because TIME returns a decimal fraction of a day, which you can add to or subtract from any time.

What if my times are stored as text instead of as time values?

Excel will not calculate with text. If your subtraction formula returns an error or a strange result, the times may be stored as text. Select the cells, right-click, choose Format Cells, and change the format to Time. If that does not work, use the TIMEVALUE function: =TIMEVALUE(A1)-TIMEVALUE(B1) converts text that looks like a time into an actual time value that Excel can use in calculations.