Spreadsheet which will do dates before 1900

I am brand new and am switching from 25 yrs of PCs.
I do genealogy and need spreadsheet which will do dates before 1900. Excel does not. Does Open office?
I prefer low cost and am dialup so may need to get CD in snail mail.

What is Excel doing to prevent you from entering a date before 1900 in a cell?

D3v1L80Y said:

What is Excel doing to prevent you from entering a date before 1900 in a cell?

Click to expand...

That was going to be my question also. After dinner I am going to fire up Excel and see what is going on. You should be able to enter whatever in a field unless it's some Date string or something. Will check into it and get back to you. I have many Spreadsheets here and will test them all.

Do explain a bit more if you would about what is going on.

I don't have a problem and I am using Office 2004. What format are you using?

D3v1L80Y said:

What is Excel doing to prevent you from entering a date before 1900 in a cell?

Click to expand...

dtravis7 said:

That was going to be my question also...

Click to expand...

rman said:

I don't have a problem and I am using Office 2004. What format are you using?

Click to expand...

I'm using Excel 2004 for this answer.

You guys have a problem, but just don't know it yet. And the problem crops up for dates before Jan 1, 1904, not 1900. Spreadsheets store dates as numbers. For example, today (December 5, 2007) is day 37959, counted from January 1, 1904 (which is day zero). To see this for yourself, enter the number zero in a cell, then change the cell's Number format to Date, rather than General. Or enter today's date as 12/5/2007 and Excel will change the format to Date automatically. Now go back and type 0 in that cell and it'll display 1/1/04 (it means 1904 but, in typical Excel fashion, it's not clear). Since dates are numbers, you can use math functions with them (add seven to a Wednesday date to get next Wednesday's date, for example). Oh, and using negative numbers doesn't work. Entering -21,000 is displayed as -June 30, 1961. Not especially helpful.

You can enter, for example, 4/6/1830 and it will display it as that on the sheet but it will be text format, not a number and not subject to math functions, which might be important in genealogical applications (calculate age at death, e.g.).

patricci, for anything related to family history stuff, I'd leave Excel alone and use Personal Ancestral File (PAF) instead. It's super-powerful for anything you'd want to do. You can download it for free at familysearch.org. I downloaded it (~10MB) with dialup.

EDIT: oops. I just realized PAF's not supported on Mac. When I use it, I need to start Windows first. Is that a problem? You could use Fusion to run Windows on your Mac.

EDIT: I just checked NeoOffice and its "Zero-Day" is December 30, 1899, so there you go: a spreadsheet which will do dates before 1900! More importantly it will do negative numbers correctly: -21,000 displays as July 2, 1842.
NeoOffice is a 140MB download, but you could download the .dmg with broadband access (public library?) and burn it to a CD, or I saw at NeoOffice site they offer a download manager to resume long downloads...good luck!

This should overcome that issue:

How to Work With Dates Before 1900 in Excel

Here is iWork 08's Numbers doing what Neo posted about. The column is set for Time and Date with Time turned off and just Date checked. Unless I am misunderstanding what you want to do, Numbers in iWork 08 seems to work fine.

I do not want to deal with Excel but may have to with the workaround.
For the PAF answer I do not use it directly for my Genealogy but for related files I download from various places. I also created and index to church records going back to 1880. I have thousands of files and don't want to fix each one.

D3v1L80Y said:

This should overcome that issue:

How to Work With Dates Before 1900 in Excel

Click to expand...

From the frying pan into the fire...

dtravis7 said:

Here is iWork 08's Numbers doing what Neo posted about. The column is set for Time and Date with Time turned off and just Date checked. Unless I am misunderstanding what you want to do, Numbers in iWork 08 seems to work fine.

Click to expand...

I'd forgotten about iWork. The GUI is a LOT clunkier but yes, it will do all dates correctly.

I was looking for something less expensive than Iworks. Converting costs a lot.

patricci said:

I was looking for something less expensive than Iworks. Converting costs a lot.

Click to expand...

So, NeoOffice it is! (?)

I really like the GUI on iWork and Numbers but of course that is a personal preference. NeoOffice is very good and like Neo pointed out, you sure can not beat the price! FREE. Seems to do the job also.

Funny, Neo loves NeoOffice! Sorry, had to say it.