How to Calculate Time Differences in Excel

How to calculate time differences in Microsoft® Excel for intervals during the same day or for a period of more than 24 hours.

David Wallis, last updated 2022-08-30.

How Excel Views Dates and Times

To use Excel for calculations on time differences, we need to understand that Excel inextricably mixes time and date.

If you input into a cell a value that Excel considers to be a date, a time or a mix of both, then Excel treats your input as a number. That number has an integer part and a decimal part. The integer part is the date, the decimal part, the time.

The integer part of such a number relates to Excel’s built-in calendar that starts at January 1st 1900. Excel stores January 1st 1900 as the number one.

Count one for each day since January 1st 1900 to the date you have in mind and you have the number Excel treats as your date. Count to 44803 and you’d be at August 30th 2022:

You can see Excel at work on a date by inputting a number or a date into an empty cell and then switching that cell’s number format between Number and Short Date.

The decimal part of a number represents time as a fraction of 24 hours. .66666 represents 3:59:59 PM — very nearly two-thirds of the way through a day, at 15 hours, 59 minutes and 59 seconds. Add another decimal place, i.e. .666666, to achieve 4:00:00 PM exactly:

Choose a format for your dates and times from the Date, Time and Custom Categories in Excel’s Format Cells dialog box:

The custom format I’ve used in the table of examples above is dd mmmm yyyy hh:mm:ss AM/PM

Time Intervals—What’s Going on in Excel

If your departure and arrival times are in the same day, and you input them in the hh:mm style, then things are straightforward:

Wobbles set in when the arrival is next day:

If you set the format on cell C2 to General, you see that Excel regards the result of the formula as -2.91666 recurring:

That result stems from how Excel manages dates and times as explained in the previous section.

The departure and arrival times in the example above are stored by Excel as date/time values, based on its calendar. Applying a custom number format to the 09:30 and 02:30 we see the full dates and times in terms of Excel’s calendar:

To display the actual numeric values Excel is using, give the departure and arrival time the General number format:

Because the input was of time alone, Excel assigns zero to the integer part of the number. Hence you arriving before you’ve departed!

Solutions

To be certain that your calculation of a time difference is to work as intended, you need to supply Excel with dates, not simply the times alone. Supply times alone and Excel will assume they are taken from the same day, namely day zero: January 0th 1900.

Returning to our departure and arrival example, the journey time calculates correctly if we input dates and times:

But hold on! We’ve not got it totally sorted yet. See what happens when the arrival goes out by another day:

All that’s needed is to change to [h]:mm the number format applied to the cell containing the formula:

Excel’s Hours, Minutes and Seconds

Excel assigns the value 1 to represent one day. Hence its hour is a 1 ÷ 24, which is 0.041666…

Its minute is 1 ÷ (24×60), which is 0.00069444… .

Its second is 1 ÷ (24×60×60), which is 0.0000115740741, roughly.

These awkward values are what lead to the sorts of numbers Excel is using to compute calculations involving time, as in the departure and arrival times we broke down in the interval calculations above.

Your Support for DMW TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —

Thanks, in anticipation.