Excel uses a sequence of serial numbers for dates counting upwards from January 1st 1900, which it treats as day 1.
All dates are treated as this underlying serial number, but you can apply formatting so that you can see the date in a way that is appropriate for your spreadsheet, this allows for formatting differences between countries or depending on how detailed you want your date information to be.
There are a number of reasons for why Excel uses this serial number system for dates, as well as some potential issues.
Download File
If you would like to follow along download the attachment below.
Why Does Excel Use Serial Numbers For Dates
By treating all dates as a serial number Excel can more easily perform numeric calculations with dates, so if you were subtracting the date 10/01/1900 from 01/01/1900 it would perform the calculation 10-1.
This also allows for consistency so whichever region Excel is in or however dates are formatted it always has a consistent way of calculating difference between dates.
Leap Year Bug
One issue with the serial number system is that Excel treats 1900 as a leap year when it was not.
This is because while leap years are divisible by 4, century years (those ending in 00) must also be divisible by 400 to be considered leap years, so while 2000 was a leap year, 1900 wasn't.
Because of this bug the serial numbers are actually off by 1 after February 28th 1900. This will only be an issue if you are trying to calculate the difference between two dates where one of the dates is before February 28th 1900.
Dates Before January 1st 1900
If you want to calculate the difference in dates going back prior to January 1st 1900 then Excel will return an error.
While all dates after January 1st 1900 are treated as numbers, all dates before this are treated as text only and trying to use them in calculations will result in a ‘#VALUE’ error.
How to Quickly Format Dates
Usually, but not always, the date will default to the short date format for the region you have set your Excel up in, so February 1st 2024 will show as 01/02/2024 in the UK and 02/01/2024 in the USA.
If you enter a date in a cell you can quickly change its format using the number formatting in the Home tab.
To do this click on the cell you want to change the formatting of, go to the Home tab, in the Number section click on the Number Format drop down.Â
This will drop down a list of preset number formats.
The four on the drop down that relate to dates are:
General or Number – These will show the date in its raw format as a serial number counting from January 1st 1900
Short Date – This will show the date in the day month year or month day year format depending on your location
Long Date – This will show the month name as text
This is how the output will look in Excel for January 1st 2024:
More Advanced Date Formatting
While Short Date or Long Date may be suitable most of the time it is possible to format dates in many ways in Excel.
To do this either click ‘More Number Formats…’ at the bottom of the Number Format drop down, or press Ctrl + 1.
This will open the Format Cells window. If you select Date in the Category section you will be able to select a variety of pre-set date formats, or change the location to update the pre-set formats.
How to Manually Format Dates
If you do not want to use any of the preset formats you can create your own custom date formats by selecting Custom in the Category section of the Format Cells window.
Excel uses format values to represent parts of the date.
Below is a list of how different formats would show the date February 1st 2024, which was a Thursday.
These date formats can be combined to show the dates in different formats
Below is an example of how different format combinations would show the date February 1st 2024.
Conclusion
Understanding how Excel handles dates can help you format date outputs to better fit your spreadsheets audience, whether that means changing the date format to match a different countries or displaying it in a way that allows for better presentation.
Regardless of the format you choose the underlying serial number will always remain the same allowing Excel to continue making calculations.
Excel only recognizes dates from January 1st 1900, all dates before this are treated as text and cannot be used in calculations.
There is also a bug where 1900 is treated as a leap year when it wasn't.
Comments