The fastest way to find time differences in Excel

Excel calculates time differences by subtracting one time from another. When you subtract a start time from an end time in the same cell, Excel gives you the result as a decimal — usually something like 0.5 for half a day. To see that result as hours, minutes, and seconds instead, you format the cell as time or multiply the result by 24 to convert to hours.

The simplest setup uses three columns: a start time in column A, an end time in column B, and a formula in column C that reads =B2-A2. If your times span midnight (like 11 PM to 2 AM), you need to add 1 to the formula so Excel knows the end time is the next day: =(B2-A2)+1. Format column C as time, and you see the difference displayed as hours:minutes:seconds.

Key Takeaways

  • Subtract the start time from the end time in a single formula: =B2-A2, then format the result as time to see hours:minutes:seconds.
  • When times cross midnight, add 1 to the formula so Excel counts the end time as the next day: =(B2-A2)+1.
  • To display the result in hours only (like 8.5 for eight and a half hours), multiply the formula by 24: =(B2-A2)*24.
  • Use the TEXT function to display time differences in a specific format without changing the cell format: =TEXT(B2-A2,"h:mm:ss").

Setting up your time columns correctly

Start by entering times in a consistent format. Excel recognizes times typed as 9:30 AM, 09:30, or 21:30 (military time). If you paste times from another source, they sometimes arrive as text instead of actual time values — Excel will not calculate with text. To check, click a cell with a time and look at the formula bar at the top; if you see an apostrophe before the time (like '9:30 AM), it is text and you need to convert it.

The easiest conversion is to use Find & Replace. Press Ctrl+H, leave the Find field empty, type a space in the Replace field, and click Replace All. This forces Excel to re-read the cells as values. If that does not work, create a helper column with the formula =TIMEVALUE(A2) to convert text times to real times, then copy the results and paste them back as values.

Once your times are real values, put your start times in one column and end times in another. Label them clearly — "Start Time" and "End Time" — so you do not reverse them by accident when you write the formula.

The basic subtraction formula and formatting

In the cell where you want the result, type =B2-A2 (replacing B2 and A2 with your actual cell references). Press Enter. Excel calculates the difference, but it displays as a decimal like 0.333333, which represents one-third of a 24-hour day.

To see this as hours and minutes, right-click the cell, select Format Cells, choose the Time category, and pick a format that shows hours and minutes — usually [h]:mm or h:mm:ss. The brackets around the h in [h]:mm tell Excel to display hours beyond 24 (so 30 hours shows as 30:00, not 6:00). Without brackets, Excel wraps at 24 hours and shows 30 hours as 6:00.

If you want the result in decimal hours (like 2.5 for two and a half hours), multiply by 24 instead of formatting: =( B2-A2)*24. This is useful when you need to feed the number into another calculation, like multiplying by an hourly rate.

Handling times that cross midnight

When your end time is earlier in the clock than your start time — like starting at 11 PM and ending at 2 AM — Excel subtracts and gets a negative number. The formula =(B2-A2)+1 fixes this by adding one full day (which Excel stores as 1) to the result. This tells Excel the end time is on the next calendar day.

This works only when you cross midnight once. If you work a shift that spans two midnights (very rare), you would add 2 instead. For most time-tracking, crossing midnight once is the only case you encounter.

Test your formula with a known example: if you start at 11 PM and end at 2 AM, the difference should be 3 hours. Type 11:00 PM in A2, 2:00 AM in B2, and =(B2-A2)+1 in C2, then format C2 as time. You should see 3:00 or 03:00 depending on your format.

Using the TEXT function for custom display

The TEXT function lets you display a time difference in any format you want without changing the cell format. The formula is =TEXT(B2-A2,"h:mm:ss"). The part in quotes is the format code: h shows hours, mm shows minutes, ss shows seconds. You can customize it — "h:mm" shows only hours and minutes, "hh:mm:ss" pads hours with a leading zero (so 3:30:45 becomes 03:30:45).

TEXT is useful when you want different cells formatted differently, or when you need the result as text to paste into another program. The downside is that TEXT returns text, not a number, so you cannot use the result in further calculations. If you need both — a formatted display and a usable number — create two columns: one with the raw formula and one with TEXT applied to it.

Calculating total hours across multiple rows

If you have many time entries and want to sum the total hours worked, create a helper column with the time-difference formula for each row, then sum that column. For example, if your time differences are in column C (rows 2 through 10), type =SUM(C2:C10) in a cell below. Format that cell as time to see the total as hours:minutes:seconds, or multiply by 24 and format as a number to see decimal hours.

A common mistake is forgetting to format the sum cell. If you sum formatted times and do not format the result, you see a decimal like 0.458333, which is hard to read. Format it as time when ready after entering the SUM formula.

If your times cross midnight and you used the +1 adjustment, the sum automatically accounts for it — each row adds 1 day only when needed, so the total is correct.

Troubleshooting common calculation errors

If your formula returns a negative number or a very large number, check whether your times are in the right order (end time minus start time, not the reverse) and whether you need the +1 adjustment for midnight crossings. A negative result almost always means the subtraction is backwards or midnight is involved.

If the result shows as a decimal like 0.125 instead of formatted time, the cell is not formatted as time. Right-click, select Format Cells, choose Time, and pick a format. If the result shows as ##### or a long string of numbers, the column is too narrow — double-click the border between column headers to auto-fit the width.

If your times came from another program and the formula returns 0 or an error, the times are probably stored as text. Use the TIMEVALUE conversion method described earlier, or copy the times, paste them into Notepad, then paste from Notepad back into Excel — this often forces Excel to recognize them as real times.

Frequently Asked Questions

How do I show time difference in hours only, not hours:minutes:seconds?

Multiply the formula by 24: =(B2-A2)*24. Format the cell as a number with decimal places. This shows results like 8.5 for eight and a half hours. If you want whole hours only, use =(B2-A2)*24 and format as a number with zero decimal places.

What if my start and end times are in the same cell?

You cannot calculate a difference from a single cell. You must separate the start and end times into two different cells first. If they are combined like "9:00 AM - 5:00 PM" in one cell, use formulas to extract each time into its own column, then subtract.

Can I calculate time differences if the times are in different time zones?

Excel does not have a built-in time zone function, so you must convert both times to the same zone first. Subtract the hours difference manually (like adding 5 hours if one time is in EST and the other in CST), then do the subtraction. Or use a helper column to convert both to UTC, then subtract.

Why does my sum of times show as a time instead of total hours?

The SUM formula is working correctly — it is just formatted as time. To see total hours as a decimal, right-click the sum cell, format it as a number, and multiply the formula by 24: =SUM(C2:C10)*24. To keep it as time format but see hours beyond 24, use the [h]:mm format code.