pre-1900 dates in Excel 2000

rraby · May 17th, 2002, 08:43 AM

Not sure if you can help, but i'm trying to work with a time series data file in excel. My problem is that excel will not recognise any dates from before 1/1/1900 and my series runs back to 1850. Have you had any experience with this problem and how to get around it?

Yogi Anand · May 17th, 2002, 08:53 AM

Quote:

On 2002-05-17 07:43, rraby wrote:
Not sure if you can help, but i'm trying to work with a time series data file in excel. My problem is that excel will not recognise any dates from before 1/1/1900 and my series runs back to 1850. Have you had any experience with this problem and how to get around it?

To work with dates before Jan-1-1900, you will have switch over to 1904 date system. For this go to TOOLS|OPTIONS|CALCULATION and then in workbook options select 1904 date system.

Regards!

_________________
Yogi Anand
Edit: Deleted inactive web site reference from hard code signature line

[ This Message was edited by: Yogi Anand on 2003-01-19 16:44 ]

Mark W. · May 17th, 2002, 09:13 AM

See http://j-walk.com/ss/excel/files/xdate.htm

Or, store post 1700 dates as text (e.g., A1:B1 contains {"1/1/1850","6/5/1851"}) and add a century (or two) to the dates to accomplish date arithmetic as follow:

=(LEFT(B1,LEN(B1)-4)&(RIGHT(B1,4)+100))-(LEFT(A1,LEN(A1)-4)&(RIGHT(A1,4)+100))

...indicates that there were 520 days between January 1, 1850 and June 5, 1851.

And...

=DATEDIF((LEFT(A1,LEN(A1)-4)&(RIGHT(A1,4)+100)),LEFT(B1,LEN(B1)-4)&(RIGHT(B1,4)+100),"M")

...shows there were 17 months between these dates.

[ This Message was edited by: Mark W. on 2002-05-17 08:31 ]

Mark W. · May 20th, 2002, 09:35 AM

A word of caution... If you opt to add a century (or two) to your post-1700 dates you should avoid the year 1900. Excel treats it as a leap year when in fact it isn't. For more on this see http://support.microsoft.com/search/...;en-us;Q214326.

[ This Message was edited by: Mark W. on 2002-05-20 14:41 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 17th, 2002, 08:43 AM	#1
rraby New Member Join Date: May 2002 Posts: 1	Not sure if you can help, but i'm trying to work with a time series data file in excel. My problem is that excel will not recognise any dates from before 1/1/1900 and my series runs back to 1850. Have you had any experience with this problem and how to get around it?

May 17th, 2002, 09:13 AM	#3
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	See http://j-walk.com/ss/excel/files/xdate.htm Or, store post 1700 dates as text (e.g., A1:B1 contains {"1/1/1850","6/5/1851"}) and add a century (or two) to the dates to accomplish date arithmetic as follow: =(LEFT(B1,LEN(B1)-4)&(RIGHT(B1,4)+100))-(LEFT(A1,LEN(A1)-4)&(RIGHT(A1,4)+100)) ...indicates that there were 520 days between January 1, 1850 and June 5, 1851. And... =DATEDIF((LEFT(A1,LEN(A1)-4)&(RIGHT(A1,4)+100)),LEFT(B1,LEN(B1)-4)&(RIGHT(B1,4)+100),"M") ...shows there were 17 months between these dates. [ This Message was edited by: Mark W. on 2002-05-17 08:31 ]

May 20th, 2002, 09:35 AM	#4
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	A word of caution... If you opt to add a century (or two) to your post-1700 dates you should avoid the year 1900. Excel treats it as a leap year when in fact it isn't. For more on this see http://support.microsoft.com/search/...;en-us;Q214326. [ This Message was edited by: Mark W. on 2002-05-20 14:41 ]