The simplest way to subtract times in Excel

To find how much time passed between two moments, subtract the earlier time from the later time in Excel. If your start time is in cell A1 and your end time is in cell B1, type =B1-A1 into an empty cell. Excel will show you the difference — but it will probably look wrong at first, because Excel stores time as a decimal number, not as hours and minutes.

When you subtract 9:00 AM from 5:00 PM, Excel gives you 0.666667 instead of 8 hours. That decimal is correct — it represents 8 hours as a fraction of a 24-hour day — but you need to format the cell to see it as time. Right-click the cell with your result, choose Format Cells, click the Number tab, select Time from the Category list on the left, and pick a format that shows hours and minutes. Now the same cell will display 8:00:00 instead of the decimal.

Key Takeaways

  • Subtract the earlier time from the later time using a straightforward formula like =B1-A1, then format the result as time to see hours and minutes instead of a decimal.
  • If your times cross midnight (like 11 PM to 2 AM), add 1 to your formula to account for the day change: =(B1-A1)+1.
  • To convert a time difference into total hours, minutes, or seconds, multiply the decimal result by 24 for hours, 1440 for minutes, or 86400 for seconds.
  • The TEXT function lets you display time calculations in any format you want without changing how Excel stores the number: =TEXT(B1-A1,"h:mm").

Fixing times that cross midnight

When your end time is earlier in the day than your start time — like clocking out at 2:00 AM after starting at 11:00 PM — Excel gives you a negative number or a strange result. This happens because Excel thinks you are subtracting a later time from an earlier one.

To fix this, add 1 to your formula to account for the day boundary: =IF(B1<A1, (B1-A1)+1, B1-A1). This formula checks whether the end time is smaller than the start time. If it is, it adds 1 (which represents one full day). If it is not, it just subtracts normally. Format the result as time, and you will see the correct hours worked.

Converting time differences to hours, minutes, or seconds

Sometimes you need the answer as a single number rather than as hours:minutes format. If your time difference is in cell C1 and formatted as time, you can convert it to different units by multiplying.

To get total hours, use =C1*24. To get total minutes, use =C1*1440. To get total seconds, use =C1*86400. These numbers work because Excel stores time as a decimal fraction of a day — so multiplying by 24 converts that fraction into hours, multiplying by 1440 converts it into minutes (24 hours × 60 minutes), and multiplying by 86400 converts it into seconds (24 × 60 × 60).

If you want to see the result as a whole number without decimals, right-click the cell, choose Format Cells, select Number from the Category list, and set Decimal Places to 0.

Using the TEXT function for custom time formats

The TEXT function lets you display a time calculation in any format you want without changing the underlying number. This is useful when you want to show time in a specific way for a report or spreadsheet that other people will read.

Type =TEXT(B1-A1,"h:mm") to show hours and minutes. Use =TEXT(B1-A1,"h:mm:ss") to include seconds. Use =TEXT(B1-A1,"[h]:mm") (with square brackets around the h) if your time difference is longer than 24 hours and you want to see the total hours instead of just the hours portion of a day. The TEXT function is also safer than formatting because it converts the result to text, so you cannot accidentally use it in another calculation.

Calculating elapsed time across multiple rows

If you have a column of start times and a column of end times, you can calculate the difference for all rows at once. Put your formula in the first empty cell next to your data — for example, if start times are in column A and end times are in column B, put =B2-A2 in cell C2.

Then click on cell C2 and look for the small square in the bottom right corner of the cell. Double-click that square, and Excel will copy the formula down to every row that has data in columns A and B. Format the entire column C as time, and all your results will display as hours and minutes. If some of your times cross midnight, use the IF formula from the earlier section instead, and Excel will explore that logic to every row.

Handling times entered as text instead of actual time values

Sometimes times in your spreadsheet will not subtract correctly because they are stored as text rather than as time values. This often happens when you copy times from an email or a website. You will notice the problem when your subtraction formula returns an error or a strange number.

To check whether a cell contains text or a time value, click on it and look at the formula bar at the top. If the cell shows something like "9:00 AM" but the formula bar shows it with an apostrophe at the start (like '9:00 AM), it is text. To convert text times to real time values, use the TIMEVALUE function: =TIMEVALUE(A1). Then use that converted value in your subtraction formula. Alternatively, use Find & Replace to remove any extra spaces or characters that might be preventing Excel from recognizing the entry as time.

Common mistakes when calculating time in Excel

The most common error is forgetting to format the result as time. Your formula is correct, but the cell displays a decimal like 0.333333 instead of 8:00:00. Always format the result cell as time when ready after entering your formula.

Another frequent mistake is using 24-hour time (like 17:00 for 5 PM) in a spreadsheet that expects 12-hour time with AM/PM, or vice versa. If your subtraction gives an unexpected result, check whether all your times use the same format. You can also accidentally include seconds in one time but not the other — 9:00:00 AM minus 9:00 AM will give you a tiny decimal that rounds to zero. Make sure all your times have the same precision.

Frequently Asked Questions

Why does my time calculation show a decimal instead of hours and minutes?

Excel stores time as a decimal fraction of a 24-hour day, so your formula is working correctly — you just need to format the cell as time. Right-click the cell, choose Format Cells, select Time from the Category list, and pick a format that shows hours and minutes. The same number will now display as time instead of a decimal.

How do I calculate time that goes past midnight?

Use an IF statement to check whether the end time is earlier than the start time: =IF(B1<A1, (B1-A1)+1, B1-A1). This adds one day to the calculation when the end time is on the next calendar day. Format the result as time to see the hours worked.

Can I calculate time in minutes instead of hours?

Yes. After you subtract your times, multiply the result by 1440 to convert it to minutes: =((B1-A1)*1440). If you want to see it without decimals, format the cell as a number with zero decimal places.

What should I do if my times are showing as text?

Use the TIMEVALUE function to convert text to a time value: =TIMEVALUE(A1). Then use that result in your subtraction formula. If TIMEVALUE does not work, your text may have extra spaces or unusual formatting — use Find & Replace to clean it up first.

How do I show time differences longer than 24 hours?

Use the TEXT function with square brackets around the hour code: =TEXT(B1-A1,"[h]:mm"). This displays the total hours instead of wrapping back to zero after 24 hours. Without the brackets, Excel shows only the hours portion of the day.