All Collections
Excel for NetSuite Applications
CloudExtend Analytics for NetSuite
FAQ: Working with date fields when you are in region that does not use mm/dd/yyyy date format
FAQ: Working with date fields when you are in region that does not use mm/dd/yyyy date format

XLANS | Learn how to work with date formats in CloudExtend Excel Analytics in regions other than the US

Updated over a week ago

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
    For example, if you want to export the transaction date field use TO_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.

Did this answer your question?