Results 1 to 3 of 3
  1. #1
    3 Star Lounger baumgrenze's Avatar
    Join Date
    Feb 2001
    California, USA
    Thanked 0 Times in 0 Posts
    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.


    Hier sind wir tief eingewurzelt.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts
    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. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts
    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:


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


Posting Permissions

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