The Basic Formula for Time Differences

To find the difference between two times in Excel, subtract the earlier time from the later time. If your start time is in cell A1 and your end time is in cell B1, the formula is =B1-A1. Excel will show the result as a decimal number representing the fraction of a day — so 0.5 means 12 hours, and 0.25 means 6 hours.

The decimal format is correct but not always readable. To see the result in hours and minutes instead, select the cell with your formula, right-click, choose Format Cells, click the Time tab, and pick a format that shows hours and minutes (like 37:30:55 for 37 hours, 30 minutes, 55 seconds). If you only want hours as a decimal number — useful for timesheets — multiply the result by 24: =(B1-A1)*24.

Key Takeaways

  • Subtract the start time from the end time using the formula =B1-A1, then format the cell as Time to see hours and minutes.
  • To get hours as a decimal number for timesheets, use =(B1-A1)*24 instead.
  • When times cross midnight (end time is the next day), add 1 to the formula: =(B1-A1+1)*24 for decimal hours or =(B1-A1+1) formatted as Time.
  • Use the HOUR, MINUTE, and SECOND functions to break a time difference into separate columns if you need each unit displayed separately.

Handling Times That Cross Midnight

When your end time is on the next day — for example, a shift that starts at 11 PM and ends at 7 AM — the simple subtraction gives a negative number or an error. Excel sees 7 AM as earlier than 11 PM on the same day. To fix this, add 1 to your formula to account for the day change: =(B1-A1+1)*24 if you want decimal hours, or =B1-A1+1 formatted as Time if you want hours and minutes.

The 1 represents one full day. When you add it before multiplying by 24 or formatting, Excel counts the time correctly across the midnight boundary. If you are not sure whether a particular row crosses midnight, you can use an IF statement to check: =IF(B1<A1, (B1-A1+1)*24, (B1-A1)*24). This formula says "if the end time is less than the start time, add 1; otherwise, don't."

Breaking Time Differences Into Hours, Minutes, and Seconds

Sometimes you need to show the time difference as separate numbers — 5 hours in one column, 30 minutes in another, 45 seconds in a third. Use the HOUR, MINUTE, and SECOND functions on the result of your subtraction. If your time difference is in cell C1, put =HOUR(C1) in one cell for hours, =MINUTE(C1) in another for minutes, and =SECOND(C1) in a third for seconds.

This approach works best when your time difference is already formatted as Time. If you are working with decimal hours instead, convert back to time format first by dividing by 24: =HOUR(C1/24), =MINUTE(C1/24), and =SECOND(C1/24). The result will be individual numbers you can use in reports or further calculations.

Common Mistakes and How to Fix Them

The most frequent error is forgetting to format the result cell. Your formula is correct, but Excel shows 0.5 instead of 12:00 because the cell is formatted as a number rather than time. Right-click the cell, choose Format Cells, select Time, and pick your preferred format. The formula does not change — only how Excel displays it.

Another common problem is times entered as text instead of actual time values. If your formula returns an error or an obviously wrong number, click on the time cell and check the formula bar at the top. If the time is preceded by an apostrophe (like '11:30 AM), it is text. Delete the apostrophe and re-enter the time, or use the VALUE function to convert it: =VALUE(A1). After that, your subtraction formula will work.

If you see a negative number or a time like 23:00:00 when you expect a small positive number, you likely subtracted in the wrong order (start time minus end time instead of end time minus start time) or forgot to account for midnight. Check which cell is which, and reverse the order or add 1 to your formula as described above.

Using DATEDIF for Larger Time Spans

For differences that span multiple days, weeks, or months, the DATEDIF function is clearer than subtraction. The syntax is =DATEDIF(A1, B1, "D") to get the number of days between two dates. Replace "D" with "H" for hours, "M" for months, or "Y" for years. You can also combine units: =DATEDIF(A1, B1, "D")&" days, "&DATEDIF(A1, B1, "H")-DATEDIF(A1, B1, "D")*24&" hours" will show something like "5 days, 3 hours."

DATEDIF is most useful when you are comparing dates rather than times on the same day. For simple same-day time differences, subtraction is faster and clearer. DATEDIF also does not work in all versions of Excel or Google Sheets, so test it in your spreadsheet before building a large formula around it.

Calculating Total Hours Across Multiple Rows

If you have many shifts or time periods and need to add them all up, create a helper column with the time difference for each row, then sum that column. In column C, put your formula =(B1-A1)*24 for the first row. Copy this formula down to every row with data. Then in a cell below, use =SUM(C:C) to add all the hours together. This gives you total hours worked, studied, or elapsed across all your entries.

Make sure all your times are in the same format (all 24-hour or all 12-hour with AM/PM) before you start. Mixed formats can cause Excel to misread the values. If you are copying formulas down a large spreadsheet, use absolute references for any cells that should not change: =(B2-A2)*24 for row 2, =(B3-A3)*24 for row 3, and so on. Excel will adjust the row numbers automatically when you copy the formula down.

Frequently Asked Questions

Why does my time difference show as a decimal like 0.333333 instead of hours and minutes?

Your formula is working correctly, but the cell is formatted as a number instead of time. Right-click the cell, select Format Cells, click the Time tab, and choose a time format. If you want decimal hours for a timesheet, multiply by 24 instead: =(B1-A1)*24.

How do I calculate time difference when the end time is the next day?

Add 1 to your formula to account for the day change. Use =(B1-A1+1)*24 for decimal hours or =B1-A1+1 formatted as Time. If you want the formula to work for both same-day and next-day times, use =IF(B1<A1, (B1-A1+1)*24, (B1-A1)*24).

Can I add up all the time differences from multiple rows?

Yes. Create a formula column with =(B1-A1)*24 for each row, then use =SUM() on that column to total all the hours. Make sure every row has the same formula structure so the sum is accurate.

What does it mean when my formula returns an error like #VALUE!?

Your times are likely entered as text rather than actual time values. Click on the time cell and look at the formula bar — if there is an apostrophe before the time, delete it and re-enter the time. Then your subtraction formula will work.