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.

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×