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

**purdyd** · 2011-05-14 15:14

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.

**macropod** · 2011-05-14 17:59

Unless you need the actual day/month part, you should be able to work with the years as ordinary numbers.

**purdyd** · 2011-05-15 02:59

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.

**macropod** · 2011-05-15 03:32

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.

**purdyd** · 2011-05-15 04:13

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.

**macropod** · 2011-05-15 05:10

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

**purdyd** · 2011-05-15 06:05

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.

**wdwells** · 2011-05-19 08:41

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

**purdyd** · 2011-05-19 09:23

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.

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

Thread Tools