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:
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.
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.
Dates and Times
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.
