Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Feb 2010
    Posts
    96
    Thanks
    14
    Thanked 0 Times in 0 Posts

    Excel 2003 time-series charts: pre-1900 values?

    I wish to plot some time-series data for dates prior to 1900 (typically 1800-1900), but unfortunately the usual solution - John Walkenbach's XDate (Extended Date Functions Add-In) - doesn't seem to be suitable in this case.

    Does anyone know of any alternative solutions ? TIA

    Dave.

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Unless you need the actual day/month part, you should be able to work with the years as ordinary numbers.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Star Lounger
    Join Date
    Feb 2010
    Posts
    96
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Many thanks - Ideally, I'd like greater accuracy than years, as I'm trying to determine the points in time when extrapolated lines cross the date axis.

    Dave.

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi Dave,

    In that case, you could express your dates as decimal numbers, where each day = 1/365.2425 of a year. That way you can still work with numbers and get day-level resolution. Of course, if month-level resolution is enough, just work in units of 1/12 instead.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. The Following User Says Thank You to macropod For This Useful Post:

    purdyd (2011-05-15)

  6. #5
    Star Lounger
    Join Date
    Feb 2010
    Posts
    96
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Paul - that's useful. Any idea how to plot showing 1800 dates in the chart ? (I'm presently using 1800 values plus 100 years, to beat Excel's pre-1900 limitations).

    Dave.

  7. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi Dave,

    Suppose you have an 1800's date in A1. To return that as a decimal number for the month in another cell you could have =XDateYear(A1)+XDateMonth(A1)/12. For April 1853, that would return 1800.33333. That should make charting fairly straight-forward.

    Note: I don't have the add-in installed on my own system, so I'm working from what can be seen on the XDate page: http://j-walk.com/ss/excel/files/xdate.htm
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. The Following User Says Thank You to macropod For This Useful Post:

    purdyd (2011-05-15)

  9. #7
    Star Lounger
    Join Date
    Feb 2010
    Posts
    96
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Paul - yes, that seems to work, e.g., =XDATEYEAR(E5)+(XDATEmonth(E5)-1)/12+XDATEday(E5)/365

    All I need now are some more data points ! ;-)

    Dave.
    Last edited by purdyd; 2011-05-18 at 05:09. Reason: Formula correction for month calculation

  10. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    A cautionary Tale

    Be cautious; the first sixty days in the Excel calendar are in error. Lotus123 mistakenly calculated that the year 1900 was a leap year. When Excel first launched, they advertised 100% compatibility with Lotus123 and consequently incorporated February 29 1900 (a day that didn't exist), into their calendar. I assume that the error remains as a result of backward compatibility requirements.

    XDate does not recognize this fictitious date (Feb. 29 1900).
    Last edited by wdwells; 2011-05-19 at 10:25. Reason: Added note re XDate
    Regards
    Don

  11. #9
    Star Lounger
    Join Date
    Feb 2010
    Posts
    96
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by wdwells View Post
    Be cautious; the first sixty days in the Excel calendar are in error ...
    Don - many thanks, it's useful to know about.

    Dave.

Posting Permissions

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