All Collections
CloudExtend Excel For NetSuite Applications
CloudExtend Excel Analytics for NetSuite
FAQ
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

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 use TO_CHAR({trandate},'YYYY/MM/DD')

The end 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?