I need to change the date format in a field from 01/05/2012 to 2012-01-05
I need to export it out to an excel spreadsheet, so it just can't be a visual change within filemaker.
If you only need it for the export and don't mind having a special layout in your solution for exporting, do the following. Create a layout that has the date field in question on it. In FM, format the display of the field to be as desired (yyyy-mm-dd). When you do the export, switch to this layout and then in the export options, near the bottom, left of the fields dialog, you will see a checkbox that says something like "format data as displayed". I don't have FM open to get the exact wording, but I am close. The exported date field will have the desired formatting.
If you need it in FM for some other reason, search Brian Dunning's site for custom functions. I know that there are many of them there that handle transforming dates in many formats.
Doug de Stwolinska
Doug has got it.
"Apply current layout's data formatting to exported data" in the "Specified Field Order for Export" dialog
This is a very good option requiring only a new layout with the date formatted in the right way as you describe and no additional data fields. It is especially good when you are exporting many date fields from the same table and need them all formatted this way. Simply put all of those date fields on the same layout.
Note that there is no need to put any additional fields from your export list onto the layout. Put only the ones you need to have special formatting. The rest will just use the default format.
If you only have one date field to export and creating a new layout is too much trouble, you can create a new calculated field for you to export.
If your Date field is named MyDate you can get it into the ISO 8601 YYYY-MM-DD format (with leading zeros) using the following definition for your new calculated field.
& "-" &
Right("0" & Month(MyDate); 2)
& "-" &
Right("0" & Day(MyDate); 2)
Specify "Calculation result is Text".
Thanks so much for your advice. I could not get the date to export the right format from a separate layout, so I used Tom's calculation and it worked great!
This is a curious conclusion. The suggestion was to use the "Apply current layout's data formatting to exported data" in the "Specified Field Order for Export" dialog. However, it sounds like that did not work and instead a calculation field was created to get the desired result?
I am in a similar situation, but not with a date field. I am trying to ensure that exported price data fields retain the 2-decimal point formatting that is set in the layout. I have the 'Apply current layout formatting' checked, but the data continues to be exported to CSV with a single decimal point if the 'ones' place is 'zero' (ie. 4.30 exports as 4.3).
Note: I do not format the price data fields as currency as I do not want the currency symbol in the data.
The interesting/frustrating thing about this is that is was working fine until I adjusted some of the find, sort, export scripts recently. I didn't notice anything in the changes that would effect this other than the layout used in the find/sort. I switched the layout back to the one that has the correct formatting, but the exported CSV is still showing incorrectly.
Any help would be appreciated. There are way too many price data fields to use separate calculation fields for each.
I apologize for the red herring. The problem I was having was not a problem with FileMaker but rather with NeoOffice (the app I was using to open/view the CSV files). The CSV import had a box checked to 'detect special numbers'. Apparently this was truncating any zeroes on the right side of any price data. When I looked at a preview of the CSV prior to opening I could see that the price data was indeed formatted correctly from FileMaker. Unchecking the 'detect special numbers' option fixed the problem.
Again, sorry for the false alarm. Thanks.