This issue was resolved with our May 2, 2024 update
The workaround below is not required anymore and was resolved with the May 2, 2024 release of CloudExtend Excel. Dates should be downloaded and displayed in the format stored in your NetSuite account and Excel will format dates appropriately.
Below workaround is for historical reference only. This issue was addressed with the May 2, 2024 update
In the US the date format is typically MM/DD/YYYY whereas many other parts of the world use DD/MM/YYYY format.
The Problem
When NetSuite is configured with a default date format other than MM/DD/YYYY (ie DD/MM/YYYY) two issues can occur when exporting to Excel.
Excel does not recognize the output as a date field if the DD is greater than 12 (in such a case it is converted to a text field).
Even when it is recognized as a date field the date will be incorrect in Excel, ie June 4th becomes April 6th
The Solution
If you are in a region that does not use MM/DD/YYYY format for dates the solution is to replace your native date field(s) with the text formula below in your NetSuite saved search.
Add a Formula(text) column with value as
TO_CHAR({field_id},'YYYY/MM/DD')
For example, if you want to export the transaction date field useTO_CHAR({trandate},'YYYY/MM/DD')Note if your field is date time format then use this formula (replacing fielded with your date time field)
TO_CHAR({fieldid}, 'YYYY/MM/DD HH:MI:SS AM')
The result is that Excel will recognize this field as a date field.
Below is a screenshot of the formula and the results of a saved search.
