Salesforce: Report export and open in Excel

Scenario:  

• Computer locale setting is English (Singapore) → uses DD/MM/YYYY
• Salesforce user account locale is English (United States) → uses MM/DD/YYYY

When opening the CSV file in Excel, the date will be incorrect because Excel expects the date in DD/MM/YYYY format.

Easy and Fast trick option

1. Rename the file extension from .csv to .txt

2. Open Excel

3. Open the .txt file from Excel

   This forces the Text Import Wizard; then:

   - Choose Delimited

   - Select Comma

   - Set the date columns to: MDY

This trick will only work with the date type, but not the date/time field.

Use Power Query

Another option is to use Power Query in Excel; this option works for both date and date/time fields. Here is the quick step:

1. Create a new Excel file (do not open the CSV file directly)

2. Navigate to Data tab >> Get Data >> From File >> From Text/CSV

3. Open the CSV file, click Import

4. Click the "Transform Data" button. This will open Power Query Editor

5. Select the column of the date or date/time columns

6. Right-click on the header

7. Select Change Type >> Using Locale...

8. On the pop-up window, select Data Type = Date (or Date/Time) and Locale = English (United States), click the OK button.

9. Click "Close & Load", the data will be loaded as a table in the Excel file with the correct date or date/time format. You can copy and paste it into a new sheet if you prefer not to be loaded as a table.