PDA

View Full Version : Sorting data, ie: dates


jsadler
11 July 2006, 03:02 AM
This isn't a Reunion question, but it is genealogy related.
I have a text file that is marriage data;
Marriage Applicant, Book, Page, Record No., Married Date

I'd like to sort it by Date. Now a normal person would think that Excel would be able to do this, but I haven't figured out how to do it. Excel doesn't know about dates before 1900. So it treats anything before 1900 as text.

Does anyone know how to get Excel to make use of dates before 1900? Or of a application/utility that can sort the data?

Thanks
J. Sadler

theKiwi
11 July 2006, 09:46 AM
There are a couple of options that I can think of. Depending on how proficient you are with Excel, and if you have FileMaker Pro...

1 - FileMaker Pro can correctly sort dates before 1900, so you could import the data into FileMaker Pro and do the sorting etc there.

2 - I don't know about AppleWorks - I've almost never used it, so don't know if its date sorting routines extend back before 1900.

3 - You could sort it in Excel if you "pull the data apart" and then do a 3 column sort on the components of the date. If you're OK with Excel Forumulae and Functions you can do this yourself. Otherwise I have a file I made several years ago that is set to do this. You can copy the formulae from it to your file, and make sure that the correct cell is referenced for each component of the date.

http://lisaandroger.com/ExcelDates/DatePullApart.xls.sit

Cheers

Roger

kyuck
11 July 2006, 02:50 PM
You can copy the formulae from it to your file, and make sure that the correct cell is referenced for each component of the date.

http://lisaandroger.com/ExcelDates/DatePullApart.xls.sit


Roger, I couldn't get at the file because of this:
Forbidden

You don't have permission to access /excelDates/DatePullApart.xls.sit on this server.

Apache/1.3.33 Server at lisaandroger.com Port 80

theKiwi
11 July 2006, 03:31 PM
Roger, I couldn't get at the file because of this:
Forbidden

You don't have permission to access /excelDates/DatePullApart.xls.sit on this server.

Apache/1.3.33 Server at lisaandroger.com Port 80

Sorry about that - the server I linked to is my own computer and when I checked the path to the file I used the Finder which allowed me to see the file, but there was a permissions issue with it which prevented you getting it via my Apache web server. That is now fixed, so try again here....

http://lisaandroger.com/ExcelDates/DatePullApart.xls.sit

or if you prefer a .zip file here

http://lisaandroger.com/ExcelDates/DatePullApart.xls.zip

Roger

jsadler
13 July 2006, 12:42 AM
There are a couple of options that I can think of. Depending on how proficient you are with Excel, and if you have FileMaker Pro...

SNIP

http://lisaandroger.com/ExcelDates/DatePullApart.xls.sit

Cheers

Roger

Thanks for the reply.
I don't have Filemaker. I think my wife's system has appleworks, so I'll look at it. I thought about the "pull it apart" method, but thought maybe I was overlooking something.

Maybe I'll get ambitious and write a quick and dirty program to do it.

J. Sadler

Frank Mitchell
13 July 2006, 05:10 AM
There are a couple of options that I can think of. Depending on how proficient you are with Excel, and if you have FileMaker Pro...

1 - FileMaker Pro can correctly sort dates before 1900, so you could import the data into FileMaker Pro and do the sorting etc there.

2 - I don't know about AppleWorks - I've almost never used it, so don't know if its date sorting routines extend back before 1900.

I have an AppleWorks spreadsheet which readily sort dates at least as far back as 1100.

Frank