Excel Time Difference in Seconds: How to Calculate

With a start time in A2 and an end time in B2, enter =(B2-A2)*86400 to calculate the difference in seconds. For example, if A2 is 9:15:20 AM and B2 is 10:02:05 AM, the result is 2,805 seconds.

The quickest way to calculate seconds

Excel stores dates and times as numbers: one whole day is 1, and a time is a fraction of a day. Subtracting a start time from an end time therefore gives a fraction of a day, not a count of seconds. Multiplying the difference by 86,400 converts it to seconds because a day contains 24 × 60 × 60 seconds.

For the basic calculation, put the earlier time in A2 and the later time in B2. Enter =(B2-A2)*86400 in C2. With 9:15:20 AM in A2 and 10:02:05 AM in B2, the elapsed time is 46 minutes and 45 seconds: 46 × 60 + 45 = 2,805 seconds. Set C2 to a number format if Excel displays the answer as a time instead of a number.

This works for time-only entries when the end time is later on the same day. If the cells contain full date-and-time values, the same subtraction handles differences across days. The key is to select a calculation that matches what the cells represent, especially when an interval crosses midnight.

Why Excel needs the 86,400 multiplier

Excel’s date-and-time system represents a date as a day count and the time of day as a decimal fraction. For example, noon is one-half of a day. A difference of 0.01 in the underlying value is 0.01 of a day, or 864 seconds—not 0.01 seconds.

To convert a time difference into another unit, multiply the day fraction by the number of that unit in a day. Multiply by 24 for hours, by 1,440 for minutes, or by 86,400 for seconds. These conversions are useful because the underlying subtraction is the same; only the multiplier changes.

For instance, the difference between 9:00 AM and 9:01 AM is 60 seconds. If you subtract the cells without converting, Excel has the correct duration internally, but it is expressed as a fraction of a day. Applying the seconds multiplier makes the result easier to read, compare, and use in later calculations.

Choose a formula for your time data

First decide whether your cells hold times alone or complete timestamps. Times alone contain a clock reading but no date. A timestamp contains both a date and a time, which lets Excel distinguish, for example, 11:50 PM on Monday from 12:10 AM on Tuesday.

Same-day times: Subtract the start from the end and multiply by 86,400.
Overnight times without dates: Use MOD to wrap the difference to the next day, provided the elapsed interval is less than 24 hours.
Full timestamps: Subtract the complete start date and time from the complete end date and time, then convert the result to seconds.
Fractional seconds: Keep the unrounded result if the source data has subsecond precision, or round it if your reporting rules call for whole seconds.

With start and end times in A2 and B2 on the same day, enter =(B2-A2)*86400. For example, 8:00:00 AM to 8:00:12 AM returns 12 seconds. With complete timestamps in A2 and B2, use that same formula: if A2 is 2026-11-02 23:59:00 and B2 is 2026-11-03 00:01:30, it returns 150 seconds.

Handle times that cross midnight

If A2 contains 11:58:30 PM and B2 contains 12:03:15 AM, a direct subtraction treats the end as earlier than the start. Since time-only entries do not tell Excel that the second time belongs to the following day, the direct result is negative. When the interval is known to run forward and last less than 24 hours, use MOD to wrap the difference into one day.

With a start time in A2 and an end time in B2, enter =MOD(B2-A2,1)*86400. For 11:58:30 PM to 12:03:15 AM, the elapsed interval is 4 minutes and 45 seconds, so the result is 285 seconds. The 1 in MOD represents one whole day in Excel’s time system.

MOD is not a substitute for dates when an interval could last 24 hours or longer. If the times are 8:00 AM on one day and 8:00 AM two days later, time-only values cannot reveal the two-day gap. Include the dates in the cells and use ordinary timestamp subtraction; otherwise, MOD would return zero seconds for matching clock readings.

Show the answer as seconds or as a duration

After calculating a numeric result, choose a number format that suits its purpose. To show a count such as 2,805, select the result cell and use Home > Number Format to choose Number or General. If a number format includes decimal places, adjust them to show the precision you need. A seconds count is often more convenient for totals, sorting, averages, and comparisons.

You can instead display the elapsed time as hours, minutes, and seconds. If A2 contains the start and B2 the end, set the result cell’s formula to =B2-A2, then apply the custom number format [h]:mm:ss through Format Cells > Number > Custom. The square brackets allow the displayed hour count to continue past 24; a format using ordinary h can roll over and show only the hour within a day.

Use the seconds formula when another calculation needs a numeric count. Use the duration format when a person needs to read the interval as clock-style time. Formatting changes how a value appears, not its underlying value. If you need a numeric seconds result, applying a time format to the multiplied result can make it look misleading, even though the calculation itself is correct.

Rounding, precision, and fractional seconds

Excel can store times with fractions of a second, so a seconds calculation may return a decimal. That is not necessarily an error: it can reflect milliseconds in the source data or the result of arithmetic on the stored values. Decide whether your task needs the exact calculated decimal, a rounded whole second, or a truncated value before changing the result.

With start and end times in A2 and B2, enter =ROUND((B2-A2)*86400,0) to round the elapsed time to the nearest whole second. If the interval is 1.7 seconds, the formula returns 2. If instead you simply change the cell’s displayed decimal places, Excel only changes what is shown; it does not replace the underlying value used in later calculations.

For very precise measurements, avoid rounding each row too early. For example, rounding many short intervals individually and then summing them can produce a different total from summing the unrounded intervals and rounding once at the end. Follow the precision rules required by your source or report. Excel stores numbers with finite precision, so extremely fine time measurements should not be treated as infinitely exact.

Common problems and how to fix them

The answer looks like a time: Change the result cell to General or Number if you want a seconds count. A formula can be numerically correct while its display format is confusing.
The answer is negative or shows ####: Check whether the end precedes the start, whether a date is missing, and whether the cell uses a time format that cannot display a negative time. For an overnight interval under 24 hours, use the MOD approach; for longer intervals, enter full dates.
The result is unexpectedly zero: Check that the entries are real Excel time values rather than text that only looks like a time. Imported data can contain text, extra spaces, or inconsistent formats. Re-entering a sample time in a blank cell can help identify whether the issue is the source values or the formula.
The result has a small decimal: The stored values may include fractions of a second. Keep the decimal for precision, or use ROUND when a whole-second result is required.
The overnight result is much too short: A MOD calculation assumes a forward interval shorter than one day. Use actual dates for durations of 24 hours or more.

There is also a distinction between elapsed clock time and real-world elapsed time across time-zone or daylight-saving changes. Excel date-and-time values do not carry time-zone information. Subtracting local timestamps gives the difference between the entered values, but it does not automatically adjust for a daylight-saving transition or convert between time zones. For records where those changes matter, normalize timestamps to a consistent time basis before calculating.

Frequently Asked Questions

How do I calculate the time difference in seconds in Excel?
With the start time in A2 and end time in B2, enter =(B2-A2)*86400. This subtracts the times and converts Excel’s day fraction into seconds. Use complete date-and-time values if the interval crosses midnight or may last a full day or more.

How do I calculate seconds between two times overnight?
With a start time in A2 and an end time in B2, use =MOD(B2-A2,1)*86400 when you know the interval runs forward and is shorter than 24 hours. For an interval that could be 24 hours or longer, enter the dates as well and subtract full timestamps.

Why does Excel show #### for my time difference?
One common cause is a negative result displayed with a time format, which the default date system may not show as a normal clock time. Confirm the order of the start and end values, include dates when needed, and use a numeric format for a seconds count. A column that is too narrow can also display ####; widening it is a quick check.

How can I show elapsed time longer than 24 hours?
With a start date and time in A2 and an end date and time in B2, calculate =B2-A2 and apply the custom number format [h]:mm:ss. The brackets keep the total hours visible instead of starting the hour display over at each 24-hour boundary.

How do I round the result to whole seconds?
With the start and end times in A2 and B2, use =ROUND((B2-A2)*86400,0). This rounds the calculated seconds to the nearest integer. If you only reduce the displayed decimal places, the underlying value remains unchanged.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.