Business Portal queries display the date format incorrectly when you export to Microsoft Excel


Symptoms


When you export queries in Business Portal to Microsoft Excel, some dates are formatted incorrectly. In Excel, if you view the properties of the incorrectly formatted cells in the Date column, you see that some cells use the Date setting in the Category list, and some use the Character setting in the Category list.

Note To view the cell properties, right-click the cell, and then click Format Cells.

Cause


This problem occurs if the regional setting of the computer operating system is set to a language other than English (United States).

Resolution


Use one of the following methods to resolve the issue.

Method 1: Set the regional settings to English (United States) on the Business Portal computer and change the date format in Microsoft Excel

  1. Click Start, click Control Panel , and then click Regional and Language Options.
  2. On the Regional Options tab, click to select English (United States).

  3. Click the Advanced tab, and then click to select English (United States).
  4. Start Business Portal, click a center page such as Financial, and then click Queries.
  5. Click a query, and then click Copy to Excel.
  6. As soon as the query is exported, right-click the column that has dates listed, and then click Format Cells. Make sure that the date type is using the DD-MM-YYYY format.

Method 2: Set the regional settings to English (United States) on the Business Portal computer and on each computer that will use Business Portal

  1. Click Start, click Control Panel , and then click Regional and Language Options.
  2. On the Regional Options tab, click to select English (United States).

  3. Click the Advanced tab, and then click to select English (United States).
  4. On a computer that will be using Business Portal, set the regional settings to English (Australia) or English (United Kingdom). To do this, follow these steps:
    1. Click Start, click Control Panel , and then click Regional and Language Options.
    2. In the Current Format list, click English (United Kingdom) or English (Australia).
    3. Click Customize, click the Date tab, and then make sure that the date uses the DD-MM-YYYY format.
  5. Start Business Portal, click a center page such as Financial, and then click Queries.
  6. Click a query, and then click Copy to Excel.

Status


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.