How to Calculate Time Difference in Excel (Step-by-Step)

You're staring at two time cells in Excel, the shift is over, and the formula that should settle payroll or a maintenance report is giving you a number that looks wrong. The good news is that Excel is doing the math correctly. The catch is that it stores time in a way that makes the result look strange until you format it the right way. If you've ever had a duration turn into a serial value, a negative time, or a wall of hashes, you're in the right place.

Take a minute to set up the formula the clean way, then build on it for overnight shifts, decimal hours, and reporting outputs. If you want a scheduling layer that handles recurring time windows across servers and environments, Server Scheduler is worth a look after you've got the Excel side sorted.

How Excel Stores Time and Why Subtraction Just Works

Excel treats time as a fraction of a 24-hour day, not as a standalone clock label. That's why 6 hours equals 0.25, 12 hours equals 0.5, and 18 hours equals 0.75, which Microsoft's support guidance explains in its time-difference article and related formatting notes on elapsed time and decimal hours. When you subtract one time from another, Excel is subtracting those fractions, so the math works before the display does. Microsoft's guidance on time differences in Excel

Start with two cells. Put a start time in A2 and an end time in B2, then enter =B2-A2. If the cells are still using General format, Excel may show a serial value instead of a duration, which is where people think the formula failed.

Set the result cell to a custom time format such as h:mm if you want elapsed time to appear cleanly for a shift or meeting window. Microsoft also notes two standard presentation approaches, keep the value in time format or convert it to decimal hours with a multiplier like *24 when you need a numeric result for reporting. If you want a practical spreadsheet example that shows why that choice matters for KPI-style outputs, this ROI walkthrough fits the same logic: calculate ROI in Excel.

Practical rule: subtract first, then decide whether you want a human-readable duration or a machine-friendly number.

Handling Overnight Shifts and Multi-Day Spans

The formula breaks the moment your end time is earlier than your start time. A 22:00 to 06:00 shift looks backwards to Excel if you enter only times, because the workbook sees two fractions of the same day and returns a negative result. That's not a bad calculation, it's a missing date rollover.

For a simple overnight case, you can add 1 to the subtraction, so =B2-A2+1. A more flexible pattern is =MOD(B2-A2,1), which wraps the result correctly even when the span crosses midnight or rolls through more than one day. That makes it a better default when you're tracking support shifts, maintenance windows, or other repeating blocks of time.

If your cells already include both date and time, plain subtraction usually works because Excel can see the full timestamp range. That matters for something like a Friday-evening maintenance run that finishes on Monday morning. In that case, the date is doing the heavy lifting, not a special overnight formula.

A useful comparison from scheduling workflows is to think in terms of the window, not just the clock. A guide like Headset Army's time conversion guide is helpful when you're comparing times across zones, because it reinforces the same habit, convert the moment, then calculate the duration. If you also keep recurring windows in a scheduling tool, the same logic applies when you review start and stop rules across environments. Drag-and-drop scheduling examples show why clean time windows matter once a team starts managing them at scale.

If both cells are pure times, use MOD. If both cells already include dates, plain subtraction is enough.

Converting Durations to Decimal Hours, Minutes, and Seconds

Once the duration displays correctly, the next question is usually, “How do I get a number I can use?” For billing, dashboards, and utilization reports, decimal output is often more useful than h:mm. Microsoft's community guidance makes the core rule clear, multiply time fractions by 24 for hours, and other conversion patterns use 1440 for minutes and 86400 for seconds. Microsoft community answer on decimal hours in Excel

A shift that shows as 7:30 can become 7.5 hours with =(B2-A2)*24. If you want total minutes for a service ticket or a batch runtime, use =(B2-A2)*1440. For second-level tracking, such as a job log or automation timing, use =(B2-A2)*86400. Those multipliers are just Excel's day-based storage translated into your preferred unit.

TEXT() is the other tool worth keeping close. =TEXT(B2-A2,"hh:mm:ss") returns a clean text string that's easy to place in a log line or a report label without changing the cell format. If you're pairing the result with another field, such as a status message or time stamp, that text output is often the simplest option.

Fast methods for minutes until uses the same kind of time math in a countdown context. A related Excel reference that helps when you're building reuse-friendly formulas is this bytes to GB formula guide, because it shows the same translation mindset, convert the raw value into the unit the audience needs.

Remember: decimal hours show the total span. If the duration crosses midnight, use a rollover-safe formula first, then multiply.

Fixing Negative Times and the 1904 Date System

A negative duration or a row of ##### usually means the workbook's date system is fighting your formula. Excel's default 1900 date system does not represent times before the base date cleanly, so a wrapped result can look broken even when the arithmetic is fine. That's why a copied file sometimes behaves differently from the workbook you built yourself.

The structural fix is the 1904 date system. You'll find it in File → Options → Advanced → Use 1904 date system, and it changes how the entire workbook interprets dates and times. That makes it a workbook-level decision, not a single-cell patch, and it shifts existing dates by four years and one day, so don't flip it casually. A practical reference for that kind of workbook-level date handling is this file date changer guide.

If you don't want to change the workbook's date system, you still have two reasonable patterns. Use =MAX(0,B2-A2) when negative results should show as zero, or use =IF(B2<A2,B2+1-A2,B2-A2) when you want to preserve the default system but correct the rollover in the formula. That keeps the result readable without changing how every existing date in the file behaves.

The main thing to separate is display trouble from date-system trouble. Formatting can hide the output, but the 1904 toggle fixes the foundation when Excel's default system is the primary blocker.

Display Tips, Custom Formats, and Common Pitfalls

A correct formula with the wrong format still looks wrong, so the cell format matters just as much as the subtraction. Use h:mm for elapsed time under a day, [h]:mm for totals that may run past 24 hours, and hh:mm:ss when you need seconds in the display. The square brackets in [h] tell Excel to keep counting total hours instead of rolling back to 1 after a full day.

A common trap is h:mm AM/PM. That format is fine for clock time, but it will make a 25-hour result look like a normal clock readout instead of a duration, which hides the problem instead of solving it. If you're reviewing a long runtime or a multi-shift total, use [h]:mm so Excel shows the accumulated hours correctly.

Another issue comes from regional settings. Some systems expect a semicolon where others expect a comma, and pasted formulas can fail if the separators don't match the workbook's locale. Midnight also causes confusion, because entering 24:00 instead of 00:00 can throw off a formula that expects a real next-day boundary.

Operational dashboard formatting tips are useful here because the same display discipline applies when you want time values to scan cleanly in a report. If a duration doesn't look right, check the formula, the cell format, and whether the workbook is using the date system you expect.

Summing Durations, Troubleshooting, and Quick FAQ

Once the individual duration formulas are working, you can add them with SUM(). The important part is the display format on the total cell. Use [h]:mm if the combined time can go past 24 hours, because otherwise Excel wraps the visible result back to the start of a day and a long total looks shorter than it is. IFERROR() helps keep a report sheet tidy when one row is blank or a pasted value is malformed, so the whole summary does not fill with error text.

Three breakage patterns show up again and again. A cell may contain text instead of a real time value, which makes subtraction behave unpredictably. Two workbooks may also use different date systems, so pasted results can look wrong even when the formula itself is fine. A third case is the most common with imported data, a time stamped as text from another system, which Excel cannot treat as a duration until it is converted.

Troubleshooting check: if a formula works in one workbook and fails in another, inspect the source format before you rewrite the formula.

For FAQ-style edge cases, NETWORKDAYS is the right tool for skipping weekends because it counts business days instead of raw elapsed time. If a shift includes lunch, split the day into two parts and subtract each block separately, then add the results. For time zones, convert both timestamps into the same zone before you calculate the difference, because Excel will otherwise give you a correct answer for the wrong local moment.

If you need to count only the workdays between a start and finish date, NETWORKDAYS can do that directly, and it avoids the trap of trying to fake a calendar rule with a plain subtraction formula. A split shift works the same way. For example, if a morning block runs until lunch and the afternoon block starts after lunch, calculate each block on its own line, then add them together. That keeps the break out of the total without relying on hidden adjustments that are easy to miss during review.