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