prehistoric time series

[Guest]

I want to make a diagram with a time series that goes from -1000000 to
present. Hov can I accomplish that, since for MS the time starts at 1900?
Moreover, I want to display the dates as BP (before present) How could that
be done?
FG

 

[Arvi Laanemets]

Hi

For such interval, your times are probably years. When yes, then you can
simply use numeric year values instead of dates.

To convert a date to year number, use the formula (with date p.e. in cell
A2)
=YEAR(A2)
and format as General

To conert a tate to yer number BP, use the formula
=YEAR(A2)-YEAR(TODAY())
and format as General
etc.

Create your diagram as XY chart with years as X-axis values

 

[Guest]

Hi

For such interval, your times are probably years. When yes, then you can
simply use numeric year values instead of dates.

To convert a date to year number, use the formula (with date p.e. in cell
A2)
=YEAR(A2)
and format as General

It didn't work. XL returned everything below 1 AD as 'invalid', and year 1
as '1900'

Do you have another method to make XL understand that there was a time
before 1900?

FG

 

[Arvi Laanemets]

Hi

You may try to use 1904-year system (from menu: Tools.Options.Calculation),
but there is a lot of disadvantages - you have to convert your existing
dates, maybe some date functions don't work properly (especially with
negative dates), etc.