Convert date format from imported excel sheet

[Agentshevy]

I recently imported records from an excel sheet into my filemaker solution. I just noticed that the dates in the excel sheet were entered in this format  1996-04-19

and as a result, all my date calculations are not working. For example, the Age and other functions. Manually changing the date to 4-19-1996 resolves it. 

Is there a way i can do this without manually going to each record? because there are a lot of records to deal with.. 

Thanks for your anticipated response

[siroos12]

Shevyshevy,

 

3 scenarios:

1- Use a script to loop through records and:

1-1 set a variable to store current data in your date fields 

1-2 then process it to reformat it

1-3 set the date field with new formatted date.

2- Set your PC date format exactly the same format as the  1996-04-19   (date format in source excel file) and then make a clone copy of your database file, Now you can import all records from your current file to the clone copy of it. It should do the job. Remember when you create a file or make a clone copy, Filemaker take the current date format of your PC as default format for date fields.

 

3- If you don't like this format for dates, then

3-1 make a UUID text field in your table if you don't have it already.

3-2 replace the the field content with "get(uuid)".

3-3 Export all records to excel.

3-4 change date format in excel.

3-5 import back the excel file using UUID field as a match criteria and update existing records in your database.

 

Hope that help !

 

Good luck.

Edited April 8, 2016 by siroos12
3rd scenario added

[Agentshevy]

thanks... I think the second scenario would be fine... and thanks for the information about filemaker and PC date format..

[comment]

1 hour ago, shevyshevy said:

I think the second scenario would be fine...

You should understand that the second scenario will leave you with a file whose date format is YYYY-MM-DD. If that's not the date format you want to use, you should not take this route. It will also screw up any valid dates you had before importing the Excel file.

The simple way to solve this is find the imported records (e.g. by searching for invalid dates in the date field), then replace the date field's content by a calculated value =

Let (
v = Substitute ( Datefield ; "-" ; ¶ )
;
Date ( GetValue ( v ; 2 ) ; GetValue ( v ; 3 ) ; GetValue ( v ; 1 ) )
)

 

[Agentshevy]

Thanks Comment for this guide, I was about to point out that my other previous entered dates have now been screwed.