I have a database of some 6000 plus church burial records. These have been recorded as a date field for day and month (e.g. 22-Jan) and a second field for year (e.g 1775) All this data, plus data in a lot of other fields, is being transferred to a new program, for which the data needs to be in 4 fields - field 1 = date in the format of DD/MM/YYYY, field 2 = DD, field 3 = MM and field 4 = YYYY.

For starters I tried using search and replace to change all( for example) -Feb to /02, but that fails to find any. I also tried to convert the date fields to text but that resulted in dates such as 17-Feb becoming 43148. Once I get past this issue I hope that search and replace and concatenating will allow me to create the other filelds.



Any pointers as to how to achieve this?

Are the dates (e.g. 22-Jan) date formatted? My guess is they are and are actually 20th or 21st century dates (thus the need for the year field since Excel can't handle dates prior to 1/1/1900 as it does dates thereafter).

If that's the case, then Field 2 can be populated with the Day function and Field 3 can be populated with the Month function. Obviously, Field 4 can be directly populated. Field 1 is then just a concatenation of these.

At any rate, if you post a sample of your sheet, it would make it easier to see how it needs to be done.

Thanks for the reply. In the meantime I found a way round this - a bit laborious but it worked. I copied the columns to a Word table, and converted the table to text. Once in Word I was able to use search and replace to change the months to a numeric representation (eg 22-Feb to 22 02), then split the date into the DD and MM. The YYYY I already had so I was easily able to produce a table with the 4 columns in Word. That was then pasted back into the Excel database.

Access allows to enter dates starting from January 1st 100.

Nice solution !

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post