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

1. ## 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. Unless you need the actual day/month part, you should be able to work with the years as ordinary numbers.

3. 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. 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.

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

purdyd (2011-05-15)

6. 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. 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

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

purdyd (2011-05-15)

9. 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.

10. ## 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).

11. Originally Posted by wdwells
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
•