Date format

Replies to This Discussion

Permalink Reply by WibblingJo on July 14, 2009 at 1:49pm

I would do something like this (assuming that your 31 03 1737 is text)

(I've tried to attach a file so let me know if it doesn't work)

Jo

Attachments:

• Date Split.xls, 14 KB

Permalink Reply by Robert Newman on July 15, 2009 at 2:25am

Thanks Jo, what a great group to belong to. Now off to play!

Permalink Reply by WibblingJo on July 15, 2009 at 10:54am

Glad the spreadsheet worked ok
Let me know if you have any other questions

Jo

Permalink Reply by John Keith Coldwell on July 15, 2009 at 10:05am

Robert:
This is an extract from a recent article in the Journal of the Genealogical One-Name Society:

The ancestry birth search results include the date in the Excel date format and presented as “Month-Year” (e.g. Jan-04). It is more useful to have the year converted to a number for ease of calculating dates (e.g. year died from age at death). Unfortunately this is not straightforward and special formulas are required. The year and month can be separately extracted in Excel using a formula to extract the month (3 letter abbreviation):

=TEXT(A1+0,"mmm")

and for year:

=VALUE(TEXT(A1+0,"yyyy"))

The formula are placed in two new columns next to the extracted Month-Year in cell A1 (adjust the formula if not in A1) and then copy down over all the rows. The date Jan-04 is then shown as Jan in the month column and 2004 in the year column. Change results to values.

John

Permalink Reply by Robert Newman on July 15, 2009 at 10:22am

Hi John,

Thanks for this. I do in fact have the article, but find it quite difficult. I was a maths and computer studies teacher for many years but the brain is now finding this type of thing hard!!!

Jo sent me a practical example which has worked brilliantly, so joining this group has already paid off for me.

I hope to learn more techniques.

I will go and give the article another read. I did download the spreadsheet without any problems!!!

Keep up the good work.

Robert