Jump to content

Convert date format from imported excel sheet


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

Recommended Posts

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

Link to comment
Share on other sites

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 by siroos12
3rd scenario added
  • Like 1
Link to comment
Share on other sites

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 ) )
)

 

  • Like 1
Link to comment
Share on other sites

This topic is 2269 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
 Share

×
×
  • Create New...

Important Information

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