How Excel stores dates and times

To understand how Excel formats dates and times we need to look at how Excel stores dates and times. In Excel, both dates and times are just numbers.

Let's take a look.

Here we have a worksheet with three tables: one for dates, one for times, and one for dates with times. Let's look at the dates first.

In Excel, all dates are numbers, and the first day in Excel's date system is January 1, 1900.

To illustrate, let's use a formula to set column C equal to column B.

Now let's format column C with the General format so that we can see the numbers without date formatting.

We can see that January 1, 1900 really is equal to the number 1—the first date in Excel's date system. And January 1, 2002 is equal to the number 37,257. That is, January 1, 2002 is 37,257 days after January 1, 1900.

Dates in the future are handled in the same way. April 15, 2020 is equal to the number 43,936.

Now let's look at time. Since one day is equal to the number 1, one hour is equal to 1 divided by 24 (1/24).

Let's again look at these values in General format using column F. 

In the same way that dates are integers, we can see that times are actually decimal numbers that increase throughout the day. Noon is .5, and 6 PM is .75—three quarters of the way through the day.

Now let's look at the last table, Date and Time.

As before, we'll set up column C to look at the values in column B without date formatting.

We see that dates with times are just a date value added to a time value. So, noon on January 1, 2002 is equal 37,257 plus .5.

Whenever you're working with a date or time, keep in mind that underneath is just a number that you can look at anytime by applying the General format.