Suppose you have a document with a date and time column. Calculating time differences is easy, you can simply subtract an earlier time from a later one1. In the screenshot below, column C shows the difference between the time in consecutive rows (column B). However, you can see that this simple approach fails just after midnight:
The time difference in row 4 is negative and also wrong. This becomes very clear when you switch to a 24h time format and enable negative times2:
Solution: Include the date in the calculation
Internally, dates are represented as whole numbers and times as fractional numbers smaller than 1 (1 would be 24h = 1 whole day). This means you can simply add date and time! Therefore, a better formula to calculate time differences is:
=A2+B2-(A1+B1)
By including the date, this formula works fine after midnight.
What if you don’t have a date column?
You can use a clever approach I found here:
=A3-A2+(A3<A2)
A3-A2 is the simple time difference calculation we used in the beginning. (A3<A2) returns TRUE only when the next day starts and the time is “smaller” than in the row above (see row 5, column C). What makes this work for our purposes it that TRUE is evaluated as 1 while FALSE is 0 (see column D). As mentioned above, a date/time value of 1 corresponds to 1 day (24h).
In row 5, Excel is therefore calculating the difference between 24h and 23h 59min 59s, which is 1 second.
1 This will work if Excel correctly recognized the value as a time (not a text). You can test this with the ISNUMBER function which should return TRUE.
2 The easiest way to get Excel to show negative times is by enabling the 1904 date system in the advanced options.
3 If you want to see time values of 24h or more, use an elapsed time format with square brackets, e.g. [h]:mm:ss instead of h:mm:ss.
One thought on “Excel: How to calculate time differences beyond midnight”