Field: "Date Collected start"

I'm working with 100 year old specimens in a museum and, upon getting this job, found that the database created for updating and digitizing the collection had the date entered, for roughly 3,000 specimens, incorrectly.

In the above field, the date is entered as follows:

1893-10-21 or YEAR-MO-DA

I've created a new column named "Date" and need to change all 3,000 to a format that is either:

"yyyy/mm/dd" (1975/01/23)

or

"dd/Mon/yyyy" (23/Jan/1975)

I can do a find/replace (I believe) to change the spacer from a "-" to a "/".

the "Date" field doesn't have to be rigid in that I change the Field Type to "date". I just need to convert the dates so I can upload this information into a database program specifically designed for these specimens, thus the date format is an issue (aside from being visually confusing).

A side note: I have a lot of partial dates (only the year perhaps) and can manipulate it so that there are zeroes or another character included where nothing presently exists...so,

1895

would become

1895-00-00

After the script it would look like:

1895/00/00 or

00/---/1895

in the new "Date" field.

Also, if anyone has suggestions on how to become better at creating scripts, be it online help, books, or tutorials, I'd be very interested in what is out there and user-friendly.

Thanks a lot for any thoughts and help.

Field: "Date Collected start"

What is this field's type?

Currently it is "Text"

Why don't you define a calculation field (result is Text) =

Substitute ( Date Collected start ; "-" ; "/" )

Sorry, I'm really new at this. How do I go about defining a calculation field? I know I can specify a calculation for the "Date Collected start" or any other field. Should I enter what you wrote as a calculation? So...

Date Collected start= (insert calculation)

Ok, that was awesome. I replaced all the "-" with "/"

Thank you! That worked really well!

Okay, so I'm still trying to figure out how to use a calculation to convert date formats from:

1890/02/23 to 23/Feb/1890

Any suggestions out there?

Edited October 22, 201015 yr by Guest
Question not quite answered.