Results 1 to 3 of 3
  1. #1
    3 Star Lounger baumgrenze's Avatar
    Join Date
    Feb 2001
    Location
    California, USA
    Posts
    262
    Thanks
    6
    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.

    Thanks,

    baumgrenze
    Baumgrenze
    Hier sind wir tief eingewurzelt.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    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:
    =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
  •