Excel is very good when it comes to dealing with dates and datetimes. In this post we’ll look specifically at finding the difference between two datetimes in Excel (as opposed to the difference between two dates with regards to days, months, etc) To simplify the math on its end, it calculates a date as “the number of days since January 1, 1900.” Let’s look at an example:
Believe it or not, these are actually the same date. The only difference is that B2 is formatted as a date, while D2 is not.
How to Find the Difference between Two DateTimes in Excel
Since dates are calculated by Excel so easily, finding the difference between two dates is easy as well.
After putting March 5th into cell D2, I created a formula in F2: “=D2-B2”.
That’s it! We now have the total number of days in between those two numbers.
Example – Find the Difference between two race times in Excel
Let’s look at this problem. We have 9 racers who all started at 10:30am. They finished at very different times. We tried to quickly check their race times using a simple subtraction formula, but we get an error. This is because the times are not formatted in a proper date format.
Using Text to Columns, we separate the “PST” from the rest of the format. Next, we’ll make sure columns G and I have proper time format:
- Delete Columns H and J
- Change formatting on (now) Column I
- Select Custom format and “hh:mm:ss”. As you can guess, this formats the result in hours, minutes, and seconds.
We can now see how long it took every member of the race to finish.