What’s up with dates and times in Excel? What is a serial number? And what does the date January 1st, 1900 have to do with it? Today, we’re de-mystifying dates in Excel. Let’s do this.
Dates in Excel: Serial numbers
In the video below, you can see what happens when you change the number format of a date to General.
Weird, isn’t it? What you see here is the underlying number that Excel uses to represent the date. This number is called a serial number. But what does it mean? And why does 4/10/2020 become 43931?
Serial numbers explained
Let us explain: 43931 is the number of days that have passed since 1/1/1900 to the date 4/10/2020. That’s how Excel saves dates.
Knowing this helps us understand some of Excel’s behavior. For example, we can predict what will happen when you calculate
4/10/2020 + 1 in Excel. The result will be 43931 + 1 = 43932. And formatted as a date, you get 4/11/2020, one day later. In the same vein,
4/10/2020 - 7 will result in a serial number that is 7 days before 4/10/2020.
So now you can use the TODAY function to calculate yesterday’s date! It’s as simple as:
=TODAY() - 1
Note on dates before 1900 – You might be wondering what happens with dates before 1/1/1900. You can try this for yourself: in a cell in Excel, type 1/1/1900. You will see the number format change to Date automatically. If you then type 12/31/1899 (just one day earlier) into another cell, the number format won’t change. Excel does not recognize it as a date because it doesn’t have a serial number to use for it. The conclusion is that Excel just isn’t very useful for working with dates before the year 1900.
This is not too difficult, right? It’s just Excel’s way of representing dates. However, it becomes more complicated when we add time as well.
For dates with times, a fraction is added to the serial number to represent the time of the day. For example, 43931.00 is the serial number for the date-time combination of 4/10/2020 12:00 AM.
The fraction is the percentage of the day that has passed at that time. So at 12 AM it’s .0000 and at 12PM it’s .5000. 1 AM is at the fraction 1/24 (.041667).
Here’s an overview for some different times of the day and their corresponding fractions and decimals.
You can use these fractions either on their own to only represent a time (0.5 for 12 PM) or combined with a whole number (43931.5 for 10/4/2020 12 PM) to represent both date and time.
So by combining the whole number for the date and a fraction for the time, we can represent any date-time combination from 1900 up until now. Pretty cool, huh?
Getting the current date and current time
Excel has functions that return the current date and time so you can use them in your worksheets. This can be handy for calculating ages based on date of birth. Or calculating how many days are left before a deadline.
For the current date, you can use the TODAY function. We have a page about the Today function here.
For the current date and time, there is the NOW function. We have a page about Now as well, with examples and explanations.
We hope you understand the inner workings of Excel a little better after reading this. If you have any more questions about date-shenanigans in Excel, let us know in the comments.
Have a great day!