1 of 1 people found this helpful
This is an issue with the xlsx format, not with FileMaker.
You would need to create an auto-enter text field with the calculation of:
Year(yourdate) & "-" & Right( "0" & month(yourdate) ; 2 ) & "-" & Right( "0" & day(yourdate) ; 2 )
This link explains a bit more:
But essentially XLSX doesn't support date formatting in exported files since it natively stores dates as a numeric value.
I agree with Mike, except I would make the insertion of the "0" character conditional. If the month<10, insert it, otherwise not. Same for the Day.
maybe Mike takes the months of October, November, and December off to go skiing ??
In my experience, date formatting is 2nd only to duplicate records as far as problems with the data themselves are concerned. There are 3 elements to a date — year, month, and day — which means there are 6 possible combinations of them, and I suspect that all 6 are in use somewhere in the world. (FWIW, I favor yours, the yyyy-mm-dd version, and so does the International Standardization Organization, with its ISO 8601 standard.) Often I just throw up my hands, export the 3 date elements as separate fields, and let the recipient of the data reassemble them however they prefer.
right("0" & month;2) takes two digits from the right - no other condition needed..
for the sake of conversation,
What if you had a calculation GetAsNumber(SomeDateField) and exported that.
How would Excel deal with that?
Does Excel also interpret dates as originating on 1/1/0001?
Mike -- thanks. Most helpful. I know how Excel stores dates; I just didn't consider that as the explanation. I was misled by the fact that the other export formats handle the date correctly.
I appreciate your text field idea, but the user of the export needs the output to be an Excel date, so I'll just reformat it in Excel.
I don't agree this, .XLSX can format its values, so if FM make unformatted excel file, it is a bug or at least lack of feature.
WilliamDuncan, here is a tool for export formatted excel file (I hadn't tested it myself)
Thanks for the suggestion, but this is far more complex than I need. It only takes a few seconds to re-format the dates.
Excel saves dates as numbers much like Filemaker. A Quick fix to get the dates ready for import into filemaker would be to convert the dates in Excel to text in the Filemaker date format using:
=TEXT(CELL,"yyyy-mm-dd") (CELL being the cell containing the date) Then copy the resulting column and paste it as text. This column becomes the field that you would import as the date.