When downloading a report with a long date range and opening it in Excel, you may find that some of the dates are in date format while others are in text format.
What Causes The Issue?
This problem is most likely caused by an Excel locale setting trying to change the date format.
How To Fix The Issue
To workaround this problem, follow these steps:
- Download the report file from MarinOne and save it to your computer.
- Open Excel (A blank sheet will appear).
- Go to Data, and click From Text.
- Choose the file and click Import.
- The Text Import Wizard will appear. There's no need to do anything at Step 1 and 2, just click the Next button.
- In Step 3, select Text in Column data format.
- Click Finish, and the spreadsheet should open with a consistent date format.
- The date format is dependent on the locale of the Marin account. You can change the Locale setting under client settings.
- Click Admin module in the bottom-left.
- Click on the Clients & Users tab and the Clients sub-tab.
- Double click on the desired client to launch its settings in the Side Panel.
- Under the Geographic section, hover over the Locale field until the Edit button ('pencil' icon) appears, then select it.
- Change the locale as necessary.
- Click Save.
- If you notice that the year format is missing for any rows (i.e. showing YY rather than YYYY), there is a solution. Use the text import again, but at step 3, select Date and choose MDY from the drop-down, then click Finish. The date should now be formatted correctly.