Thread: Chart With 1800's, 1900's, and 2000's Dates

1. I have a simple Excel 2000 table that has a series of dates, the earliest is 3/3/1863 and the most recent is 5/11/2009. I'd like to plot a simple chart of the data in the table vs the dates. Excel will not recognize dates before 1900. I found John Walkenbach's XDate add-in at:

Extended Date Functions

Unfortunately I am not Visual Basic enabled. It is not clear to me that if I put the effort into understanding XDate that I will be able to

1) get Excel 2000 to recognize dates before 12/31/1899

2) create a chart using the information

I get the sense that what I want in the unformatted date column are negative numbers that increase in absolute value starting with 12/31/1899 and going back in time.

Surely this has been worked out somewhere and I just can't seem to formulate a search to find the simple answer to my question.

Thanks,

baumgrenze

2. The XDate add-in adds custom worksheet functions that let you perform calculations with text values that look like dates. But Excel itself still doesn't recognize dates before 1900, even with the XDate add-in.
If you enter text values that look like dates, Excel can use them in a chart, but it won't create a time scale axis. It'll treat the values as category values instead.
If you use negative numbers, Excel won't be able to display them as dates.

As a workaround, you could add 100 years to all dates, so that they range from 1963 to 2109. Excel can handle that, it'll just look weird.

Or you could use Microsoft Access - it handles years from 100 to 9999.

3. With that wide of a range, why not just plot based on years as integer values and calculated the approximate "fraction" of a year based on the date.

3/11/1863 could be:
=1863+YEARFRAC(DATE(2009,1,1),DATE(2009,3,11))

5/11/2009:
=2009+YEARFRAC(DATE(2009,1,1),DATE(2009,5,11))

This would make an appropriately scaled X-Axis on an XY scatter chart

Steve

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•