Convert numbers to date

[Derf]

Sorry I didn't know where to put this.

I have 3,500 records with one field that is used for a purchased date. This person used four numbers for each date such as 0793 represents July 1993 or 0411 equals April 2011. All records have this four number format, first two are month and second two are year. I can't figure out how to convert these four digits into a some sort of date field like 07/1979. I tried 'find and replace' but couldn't get it to work.

Any help would be appreciated, I don't want to type in 3,500 dates.

I'm converting a friend's baseball card inventory from a 1980's DOS machine to Filemaker Pro.

[LaRetta]

Back up first ...

I am not sure how you determine whether two-digit year is 19^th or 20^th but, assuming any cards aren't older than 1912, create a calculation (result is date) as:

Let ( [

m = Left ( n ; 2 ) ;

yr = Case ( Right ( n ; 2 ) < 12 ; 20 ; 19 ) & Right ( n ; 2 )

] ;

Date ( m ; 19 ; yr )

)

Be sure to set the result to date and leave Storage Options alone ( do NOT check 'do not store calculation results) because we want the calculation results to be stored. Exit field definitions then go back in and change the calculation to regular date field. The correct date data will be inserted into the date field.

... update ... n is your number field

[fmbug]

As I got your question that you are having Date like in four digit format "1212" but you want a calculation field which will calculate like this:

12/2012... If you want to know the answer you can visit the following link.

http://rsgss.co.in/RsgssForum/index.php?p=/discussion/3/date-prob

[comment]

how to convert these four digits into a some sort of date field like 07/1979.

"07/1979" is a text string, not a date - and it will be sorted alphabetically, not chronologically. A date field must have a complete date, including the day of month. It would be probably best to assign an arbitrary day of month to all your "dates", as shown by LaRetta (though I suspect she meant to use 1, not 19...). Then format the field to show only the month and the year.

Another option is to use two number fields, one for the month and one for the year. This may be more convenient for entering new data.

[LaRetta]

Hopefully this is a one-time conversion from improper MMYY format to proper date where future entries would be actual purchase date into true date field (including the day). And thank you for catching my typo, Michael. :)

[Derf]

Thank you all very much, it worked perfectly. I changed the 19 day to 1 and just left that in all of the records.

I tried changing the field date format to 07/2010 but when you enter new ones it wants a day so I changed it back and left it like that. This is a one time conversion and none of the dates were before 1993 so it worked great.

You saved me a lot of work, thanks so much.