In this video, we're going to learn how Excel treats dates, and time. If you often work with dates and time, you might notice what seems like some strange behavior. For example, over here we've got a date. And this is something that Excel clearly recognizes as the date. As we can see, when we use the filter drop down, it is able to identify all the components of that date. Here we have a number.
And here we have a formula that is doing simple subtraction of one minus the other, giving us an answer of zero. And as you'll notice, this is now taken on the formatting of a date. So what exactly is going on? And the answer is actually very simple. Excel uses a serial number system to store and recognize date information. The first date recognized is the first of January 1900.
And each day thereafter is simply one More. So for example, over here, the eighth of October 1986 is day 31,693. Since the first of January 1900. The appearance that we see as a date is simply a matter of formatting. If we apply a number format, for example, using Ctrl Shift, and one, we actually see that this, in fact, is a number. And that is why when we do simple subtraction, we get an answer of zero.
To convert that back into what looks like a date, we can use the shortcut of Ctrl Shift three, or use the number drop down to choose either from a short date or a long date notation. So as you can see, what seems like some strange behavior, where dates change to numbers and vice versa, is in fact the way that Excel recognizes date information system Knowing this fact that dates are in fact numbers will help you tremendously when learning all of the different date and time functions. We'll see in the next video