Microsoft Office Tutorials and References
In Depth Information
DEAL WITH DATES BEFORE 1900
DEAL WITH DATES BEFORE 1900
Challenge: Excel stores a date as the number of days that have elapsed since
January 1, 1900. This means that all the cool date functions do not work for
dates in the 1800s. This is a problem for historians and genealogists.
Solution: A formula proposed by Boller calculates elapsed days going back
to January 1, 1000. I’ve adapted this formula a bit. Boller’s original formula
solved the problem by adding 1,000 years to the date. Thus, a valid date such
as January 23, 2009, would become January 23, 3009. An invalid date such as
February 17, 1865, would become February 17, 2865. Because Excel can deal
with dates up through the year 9999, this method works sufﬁ ciently.
You need to enter a start date in A4 and an end date in B4. Use a format such as
2/17/1865 when entering dates. If your date is after 1900, Excel automatically
converts the date to a date serial number. If the date is before 1900, Excel
stores the date as text.
If the cell contains a real date, you want to add 1,000 years. An easy way
to do this is to use the EDATE function and add 12,000 months to the date.
=EDATE(A4,12000) returns a date that is 1,000 years after a valid date in
A4. Note that this function requires the Analysis Toolpak in versions prior to
Excel 2007. If you can ensure that the Analysis Toolpak is installed, you can
use =DATE(YEAR(A4)+1000,MONTH(A4),DAY(A4)) .
If the cell does not contain a real date, you need to break the date apart, add
1,000 years, put the date back together, and convert it to a real date:
• To get the left portion of the date, use =LEFT(A4,LEN(A4)-4)
• To get the year portion of the date, use =RIGHT(A4,4)
• To add 1,000 years to the date, use =RIGHT(A4,4)+1000
• To put the month, day, and year+1,000 back together, use
=LEFT(A4,LEN(A4)-4)&RIGHT(A4,4)+1000
• To convert that result back to a true date, use =DATEVALUE(LEFT(A4,
LEN(A4)-4)&RIGHT(A4,4)+1000)
You now need to selectively use either the EDATE or the DATEVALUE portion
of the formula, depending on whether Excel sees the date in A4 as text. Enter
this IF statement in cell C4:
=IF(ISTEXT(A4),DATEVALUE(LEFT(A4,LEN(A4)-4)&RIGHT(A4,4)+1
000),EDATE(A4,12000))
Copy it to cell D4 to get a modiﬁ ed date from column B.
You can now use these results with any available date functions.
Part
I

Search JabSto ::

Custom Search