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 has a default date format other than MM/DD/YYYY (ie DD/MM/YYYY) two issues can occur:

  • 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 a text formula 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 results 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?