Excel Formulae and Formats for Time Differences
When you use Excel to calculate the interval between two times — between departure and arrival times, say — you have to be canny in the way in which you input the times and the way in which you apply number format to the results of your formula.
David Wallis, last updated 2018-02-27.
Setting the Scene
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. Any date or time, or combination of date and time, that you input is interpreted by Excel according to its built-in calendar. Excel 2010, 2013 and 2016 calendars start on 01 January 1900, a date to which Excel assigns the numeric value of one.
You can test this by inputting 1/1/1990 into a cell and applying to that cell the General number format:
For each day since 01 January 1900, Excel adds one. So if you input 1/1/2019 into a cell and apply the General format, you learn that Excel regards 01 January 2019 as day 43466.
So 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 the calendar:
To display the actual numeric values Excel is using, give the departure and arrival time the General number format:
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: 01 January 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/24th, which is 0.041666…
Its minute is a 1/(24×60)th, which is 0.00069444… .
Its second is a 1/(24×60×60)th, 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.