Excel has a lot of functions, and one of the main ones is dates; excel can calculate dates for you. But it does not calculate dates before 1900, so how to excel use dates before 1900? If you need to deal with dates older than 1900, Excel’s conventional date-handling method will not be of use. There are, however, several workarounds for this issue.
Recent dates are stored in Excel as a date serial number, which enables us to sort those dates and perform date arithmetic on those dates. Because Excel’s serial number started on January 1, 1900, it is impossible to use a negative serial number with the program.
(The Macintosh starts with the year 1904 as the default date.) The default date may be changed by going to the menu bar and selecting Options, then Calculation, and then selecting or clearing the 1904 date system checkbox.)
This formula is used to demonstrate the 1900 date system.
=DATEVALUE(“Jan 1, 1900”) =DATEVALUE(“Jan 1, 1900”)
Returns the value 1, which indicates that the date serial number is the first.
On the other hand, this formula is for a day that has passed.
=DATEVALUE(“December 31, 1899”) =DATEVALUE(“December 31, 1899”)
…returns the #VALUE! error value as a result.
To suggest that this outcome is regrettable would be an understatement. Because when working with dates earlier than 1900, you will often want to perform the same two things that you would do when working with more recent dates. To be more specific, you wish to sort them and do date arithmetic on them.
Fortunately, Excel provides you with the ability to get around most of the issues associated with early deadlines. You must, however, bear in mind the possible difficulties associated with early start dates.
The Problem
We employ a completely different dating system than we used several hundred years ago in today’s society. Our calendar system is now known as the Gregorian calendar; in the past, regional versions of the Julian calendar were used by much of the Western world.
Today, one of the variations seems to be unusual. Until 1752, Lady Day (March 25) was observed as the first day of the new year across the British Empire and her colonies.
It didn’t help that various sections of the globe went through the conversion from Julian to Gregorian at different periods over many centuries. Therefore, ships’ records may state that they arrived at one port before leaving the previous one if this was true.
To demonstrate these geographical variances in the United States, consider the following.
With Spain, the states of Texas, Florida, California, Nevada, Arizona, and New Mexico became Catholic. The last Julian date was on October 4, 1582. The first Gregorian date was on October 15, 1582. To be explicit, the date of Wednesday, October 4, 1582 (Julian) was followed by the date of Thursday, October 15, 1582 (Augustus) (Gregorian).
With France came the conversion of the Mississippi Valley. The last Julian date was December 9, 1582. The first recorded Gregorian date is December 20, 1582.
Washington, Oregon, and the Eastern Seaboard joined the rest of the country in converting to Christianity. The last Julian date was September 2, 1752. The first recorded Gregorian date is September 14, 1752.
It should be noted in all three examples that specific dates did not exist in any of the three locations. For example, on the Eastern Seaboard, colonists were never exposed to the period September 3 through September 13, 1752, as was the case on the West Coast. In addition, the year 1752 started on March 25 and concluded on December 31 in their calendar.
As I shall explain, you may construct a date in Excel, such as September 10, 1752, if you choose. First, however, you will need to ensure that the data is appropriately interpreted inside your data.
The Solution
If you apply a formula in Microsoft Excel, it must result in an error since Microsoft does not support it. So instead, the date was checked in the following ways: for example, 8/5/1456 was checked using the =ISNUMBER function.
Yes, the answer is FALSE, which implies that Microsoft Excel does not recognize the date since the year is before 1900; in other words, if you write the date “1456,” the date is rendered as text rather than numbers in Microsoft Excel. I attempted the experiment many times and failed each time. Still, eventually, I discovered how to do it: multiplying the number of years by a certain amount of numbers so that it was changed into numbers. I experimented with the modest values +200, +300, and +400, but the results were inconsistent. I finally discovered the number 7000, but when I went to add it to the list, it gave me an aliasing error instead of the correct number. As a result, let us introduce two examples into the formula. If you want to calculate your age from your date of birth (start date) through your date of death (end-date), you may use other words. For example, the end date might be changed with =TODAY to calculate your age till today. The formula is quite big, but you only need to copy and paste it to work. The formula is quite big, so we will be adding the file in the post.
You can easily change the dates and get the results with our workbook, as shown in the pic.
Step 1: Open the attached workbook and copy the formula for use in your workbook or use our sheet and just enter the data into A2 and B2 cells.
Step 2: The results will be shown in the A5 cell.
Conclusion
Now you know all about the trickiness of excel and dates and how you can overcome excel use dates before 1900. All you will need is the formula in our attached spreadsheet, and you can begin calculating with years before 1900.