Jump to content

Change 20** dates back to 19**


This topic is 6548 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

Try this script:

Show All Records

Go to Record [First]


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.

Link to comment
Share on other sites

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.



Link to comment
Share on other sites

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

Link to comment
Share on other sites

From FMPro Help:


Date (month, day, year)

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


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



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.


Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 6548 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.