How to Calculate the Difference in Time in Excel

Time calculations in Excel trip up a surprising number of people — not because the math is hard, but because Excel stores time in a way that isn't immediately obvious. Once you understand the underlying logic, the formulas become straightforward and genuinely powerful.

How Excel Stores Time (The Key to Everything)

Excel treats dates and times as decimal numbers. A full day equals the value 1. So:

• 0.5 = 12 hours (half a day)
• 0.25 = 6 hours
• 0.75 = 18 hours

When you type 8:00 AM into a cell, Excel stores it as roughly 0.333. This means subtracting one time value from another gives you the difference as a decimal fraction of a day — and from there, you can display or convert it however you need.

This single concept explains why most time difference formulas in Excel are simpler than people expect.

The Basic Time Difference Formula

To find the difference between a start time and an end time, subtraction is all you need:

=B2-A2 

Where A2 is your start time and B2 is your end time.

The result will display correctly as long as you format the result cell as a time format (e.g., h:mm or h:mm:ss). Right-click the cell → Format Cells → Time → choose your preferred display.

⚠️ If your result shows a decimal like 0.208 instead of 5:00, the cell is formatted as a number, not a time. Fix the cell format and the display corrects itself immediately.

Displaying Results in Hours, Minutes, or Seconds

Sometimes you don't want a time format — you want a plain number representing total hours or total minutes. That's where multiplication comes in.

These plain-number results are especially useful when you need to sum or average time differences, because summing cells formatted as time can behave unexpectedly once totals exceed 24 hours.

Handling Time Differences That Cross Midnight 🕛

This is where many people run into trouble. If your start time is 11:00 PM and your end time is 6:00 AM the next day, a simple subtraction returns a negative number — which Excel displays as a string of #### symbols or an error.

The fix is to account for the day boundary:

=IF(B2<A2, B2+1-A2, B2-A2) 

This tells Excel: if the end time is earlier than the start time, add 1 (one full day) to the end time before subtracting.

Alternatively, if your data includes full date-and-time values (like 6/15/2024 11:00 PM), Excel handles midnight crossings automatically — because the date portion carries the day increment forward.

Using the TEXT Function for Formatted Output

If you want the time difference displayed as a readable string inside a formula (useful for dashboards or concatenated text), the TEXT function lets you control formatting directly:

=TEXT(B2-A2,"h:mm") 

=TEXT(B2-A2,"h ""hours,"" m ""minutes""") 

The second example outputs something like: 5 hours, 30 minutes

Keep in mind that TEXT converts the result to a text string, meaning you can't use it in further calculations. It's a display tool, not a calculation tool.

Using HOUR, MINUTE, and SECOND Functions

Excel includes dedicated functions for extracting individual components from a time value:

• =HOUR(B2-A2) — returns just the hours portion
• =MINUTE(B2-A2) — returns just the minutes portion
• =SECOND(B2-A2) — returns just the seconds portion

These are useful when you need to break a time difference into its components and display or use them separately. Note that HOUR caps at 23 — it won't return 25 for a 25-hour span. For differences longer than a day, the multiplication approach (*24) is more reliable.

When Date and Time Are in Separate Columns

Some data sources export dates and times into separate columns. To combine them before calculating:

=(C2+D2)-(A2+B2) 

Where A2 = start date, B2 = start time, C2 = end date, D2 = end time. Adding a date and time cell together creates a full datetime value Excel can subtract correctly.

Variables That Shape Your Approach 🔧

The right method depends on several factors specific to your situation:

• Data format — are your times stored as actual time values, or as text imported from another system? Text-formatted times won't calculate until converted using TIMEVALUE().
• Span length — differences under 24 hours behave differently than multi-day spans when it comes to display formatting.
• How results will be used — summing, averaging, or feeding into other formulas each have formatting implications.
• Excel version — older versions have minor behavioral differences, though the core time math has been consistent for many versions.
• Data source — times pulled from databases, CSVs, or other software often arrive as text or in non-standard formats, requiring cleanup before any formula works reliably.

Getting a clean, accurate time difference in Excel is usually straightforward once the data is in the right shape — but what "right shape" looks like varies considerably depending on where your data comes from and what you need to do with it next.