Share this article:

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:

dates-in-excel

 

 

 

 

 

 

 

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.

dates2

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.

giphy-downsized-large

Example – Find the Difference between two race times in Excel

dates3

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.

dates4

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:

dates5

  • Delete Columns H and J
  • Change formatting on (now) Column I

dates6

  • Select Custom format and “hh:mm:ss”. As you can guess, this formats the result in hours, minutes, and seconds.

dates7

We can now see how long it took every member of the race to finish.