Share this article:

Excel is very good when it comes to dealing with dates. 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.

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

Dates and Times

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.