RootsChat.Com
General => Technical Help => Topic started by: Lensmeister on Friday 01 June 18 12:48 BST (UK)
-
I have a HUGE database of info on DOB etc, which I have many dates Prior or 01/01/1900.
Excel is not recognising the years 1899 (or earlier) as a legitimate date ....
I used to have a macro that did actually make Excel recognise the date but I no longer have it.
Anyone able to offer a solution ?
-
Format either General or Text?
Annie
-
Yes.... use OpenOffice which is free! ;D ;D
.
.
.
-
Yes.... use OpenOffice which is free! ;D ;D
Agreed! BUT - if you use "save as" and save the file in MSExcel format then open it later with Excel, would you lose the date formatting? OR - if you save the file in OpenOffice format would you be able to open it later with Excel?
-
The dates are stored as 01/01/1899 etc.
-
Yes.... use OpenOffice which is free! ;D ;D
Agreed! BUT - if you use "save as" and save the file in MSExcel format then open it later with Excel, would you lose the date formatting? OR - if you save the file in OpenOffice format would you be able to open it later with Excel?
Don't have Excel.
Haven't used Excel for years and years.
-
Can send it to anyone who is "afraid of change"
.
.
-
The dates are stored as 01/01/1899 etc.
That's why I used 1800's dates!!
-
My excel WTFC database is 22607KB and has many macros, long formulas and look ups.
-
Open has Macros
.
.
.
-
Hard to believe anyone could design a database system which doesn't work with dates, and even harder to believe anyone still uses it when there are more grown-up options ::)
If you can't upgrade to LibreOffice which does these things so much better, you might try:
http://www.exceluser.com/formulas/earlydates.htm
Mike.
-
Format either General or Text?
Annie
Yes, set Number Format to General is the way to go.
It works OK for me (Excel 2007). Sometimes it flips itself out of General but it's easy to reset.
Dawn M
-
Hard to believe anyone could design a database system which doesn't work with dates, and even harder to believe anyone still uses it when there are more grown-up options ::)
If you can't upgrade to LibreOffice which does these things so much better, you might try:
http://www.exceluser.com/formulas/earlydates.htm
Mike.
It's only a matter of downloading OpenOffice, then starting Calc, Open File and select an existing Excel File and see how well OpenOffice works and still have the .xls file
Any changes made can still be saved as .xls so Excel can open them if needed.
-
Formatting as General or Text is probably the way to go but any specific date-related functions, filters, sorts, etc. won't be available. An option might be to split date columns formated as geneneral or text out into separate year, month and day columns (formatted as text or numbers). Depending on your database, this may make easier or more difficult to work with.
See for example https://www.ablebits.com/office-addins-blog/2016/06/01/split-text-string-excel/ (https://www.ablebits.com/office-addins-blog/2016/06/01/split-text-string-excel/).
-
Sorry, I'm getting confused (not unusual). Excel is NOT a database program even though many people seem to think it is, but that does not excuse it from formatting dates properly.
Mike.