Author Topic: Excel pre 1900 dates  (Read 1738 times)

Offline Lensmeister

  • RootsChat Veteran
  • *****
  • Posts: 748
  • https://neilsfhsite.wordpress.com
    • View Profile
Excel pre 1900 dates
« 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 ?
Hugkulstone: All
All Surname Interests here: https://neilsfhsite.wordpress.com/surnames-and-areas/

All Whitby football related research:
https://wtfchistoryhome.wordpress.com/

Austrian Genealogy Group
https://www.facebook.com/groups/272740293132370

Offline Rosinish

  • RootsChat Marquessate
  • *******
  • Posts: 14,239
  • PASSED & PAST
    • View Profile
Re: Excel pre 1900 dates
« Reply #1 on: Friday 01 June 18 12:52 BST (UK) »
Format either General or Text?

Annie
South Uist, Inverness-shire, Scotland:- Bowie, Campbell, Cumming, Currie

Ireland:- Cullen, Flannigan (Derry), Donahoe/Donaghue (variants) (Cork), McCrate (Tipperary), Mellon, Tol(l)and (Donegal & Tyrone)

Newcastle-on-Tyne/Durham (Northumberland):- Harrison, Jude, Kemp, Lunn, Mellon, Robson, Stirling

Kettering, Northampton:- MacKinnon

Canada:- Callaghan, Cumming, MacPhee

"OLD GENEALOGISTS NEVER DIE - THEY JUST LOSE THEIR CENSUS"

Offline hallmark

  • ~
  • RootsChat Marquessate
  • ****
  • Posts: 17,525
    • View Profile
Re: Excel pre 1900 dates
« Reply #2 on: Friday 01 June 18 12:56 BST (UK) »
Yes.... use OpenOffice which is free!   ;D ;D
.
.
.
Give a man a record and you feed him for a day.
Teach a man to research, and you feed him for a lifetime.

Offline philipsearching

  • RootsChat Marquessate
  • *******
  • Posts: 4,090
  • I was a beautiful baby - what went wrong?
    • View Profile
Re: Excel pre 1900 dates
« Reply #3 on: Friday 01 June 18 13:07 BST (UK) »
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?
Please help me to help you by citing sources for information.

Census information is Crown Copyright http://www.nationalarchives.gov.uk


Offline Lensmeister

  • RootsChat Veteran
  • *****
  • Posts: 748
  • https://neilsfhsite.wordpress.com
    • View Profile
Re: Excel pre 1900 dates
« Reply #4 on: Friday 01 June 18 13:10 BST (UK) »
The dates are stored as 01/01/1899 etc.

Hugkulstone: All
All Surname Interests here: https://neilsfhsite.wordpress.com/surnames-and-areas/

All Whitby football related research:
https://wtfchistoryhome.wordpress.com/

Austrian Genealogy Group
https://www.facebook.com/groups/272740293132370

Offline hallmark

  • ~
  • RootsChat Marquessate
  • ****
  • Posts: 17,525
    • View Profile
Re: Excel pre 1900 dates
« Reply #5 on: Friday 01 June 18 13:12 BST (UK) »
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.
Give a man a record and you feed him for a day.
Teach a man to research, and you feed him for a lifetime.

Offline hallmark

  • ~
  • RootsChat Marquessate
  • ****
  • Posts: 17,525
    • View Profile
Re: Excel pre 1900 dates
« Reply #6 on: Friday 01 June 18 13:16 BST (UK) »
Can send it to anyone who is "afraid of change"

.
.

Give a man a record and you feed him for a day.
Teach a man to research, and you feed him for a lifetime.

Offline hallmark

  • ~
  • RootsChat Marquessate
  • ****
  • Posts: 17,525
    • View Profile
Re: Excel pre 1900 dates
« Reply #7 on: Friday 01 June 18 13:19 BST (UK) »
The dates are stored as 01/01/1899 etc.

That's why I used 1800's dates!!
Give a man a record and you feed him for a day.
Teach a man to research, and you feed him for a lifetime.

Offline Lensmeister

  • RootsChat Veteran
  • *****
  • Posts: 748
  • https://neilsfhsite.wordpress.com
    • View Profile
Re: Excel pre 1900 dates
« Reply #8 on: Friday 01 June 18 13:20 BST (UK) »
My excel WTFC database is 22607KB and has many macros, long formulas and look ups.

Hugkulstone: All
All Surname Interests here: https://neilsfhsite.wordpress.com/surnames-and-areas/

All Whitby football related research:
https://wtfchistoryhome.wordpress.com/

Austrian Genealogy Group
https://www.facebook.com/groups/272740293132370