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 a day.
David Wallis, last updated 2020-05-21.
How Excel Views Time
To use Excel for calculations on time differences, we need to understand that Excel inextricably mixes time and date. It does this internally by managing a data, or a time, or a mixture of both date and time, in a single number.
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 that date. Count 43859 and you’d be at January 30th 2020.
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, that is, .666666, to achieve 4:00:00 PM exactly.
A number with digits to the left and right of the decimal point is how Access stores date and time together. For example, 44790.25 is August 17th 2022 6:00:00 AM. Apply a custom format that includes both data and time elements to display the date and time in one:
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!
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.