Change 20** dates back to 19**

[murray]

I've just imported a few thousand records from and FP3 file which seems that the data entry was in year 2 digit form, and all the dates of birth have changed to 20** instead of the expected 19** form.

I've been trying to find a way revert it to it's original form but as easy as I thought it would be, I can't seem to do it.

Is there an easy way that someone can help me with?

Much appreciated.

Cheers, Murray

[rdhaden]

Try this script:

Show All Records

Go to Record [First]

Loop

Set Field ["Birthdate", "Date(Month(Birthdate), Day(Birthdate), Year(Birthdate) - 100)"]

Go to Record [Next, exit after last]

End Loop

This assumes all dates are 2000 and all dates need to be 1900. If this is a bad assumption, replace Year(Birthdate) - 100) with an If or Case statement, like:

If (Year (Birthdate) / 100 = 20, Year(Birthdate) - 100), Year(Birthdate)

or whatever other test might be appropriate.

[Lee Smith]

Make sure that you have isolated just the records you are needing to change. If there are any 2000, 2001, 2002, 2003, 2004, and good 19** in there, it will change them to also.

Lee

[murray]

Thanks for that input - I'll give it a go. I was a bit dubious about the Date(**) function as I am using Australian formats (dd/mm/yyyy) and FMP lists it as Date(month,day,year) - I wasn't sure if it would interpret my entries in that format or whether it would adjust for my format.

I'll give it a go anyway.

Cheers, Murray

[-Queue-]

From FMPro Help:

Format

Date (month, day, year)

Note The list separator you use might not be a comma.

Parameters

month - the month of the year (a number from 1 to 12)

day - the day of the month (a number from 1 to 31)

year - the year (four digits between 0001 and 3000. For example, 1999 but not 99.)

Important The order of the parameters in the Date function is always Month, Day, Year, no matter what operating system or FileMaker Pro date formats you are using.

Data type returned

date

Description

Calculates the calendar date for a given month, day, and year.

The format of the result depends on the date format that was in use when the database was created. In the United States, dates are generally in the format MM/DD/YYYY. You can change the date format under Regional Settings in the Control Panel (Windows), or the Date and Time control panel (Mac OS).

You can change how the date is displayed by assigning a different date format to the field in Layout mode. Changing the formatting in this way only affects the way the data is displayed, not how it is stored.

Important To avoid confusion when using dates, always use four-digit years. For more information about how FileMaker Pro 5 handles Year 2000 issues, choose Help menu > FileMaker on the Web

Note If you type a month greater than 12 or a day greater than the number of days in a given month, FileMaker Pro adds the extra days or months to the result. For example, Date (13, 1, 2000) returns 1/1/2001.

Copyright

[murray]

Thanks -Queue-. I had read that twice but at 2.00 am it seemed a lot more confusing than it does now.

Thanks Lee that is going to work. All entries are adults so basically once I have isolated all of the 2000+ dates I will do a "Replace" using your calc.

Thanks again, Murray