How to Fix Excel Showing CSV Dates Differently on Windows and Mac
Question details
The user needs to resolve an issue where Excel displays dates in CSV files differently depending on whether the file is opened on a Windows or Mac computer.
- Product
- Microsoft Excel
- Device & OS
- Windows and macOS
- Scenario
- Opening and viewing CSV files containing date values across different operating systems.
- Observed behavior
- Date formats like DD/MM/YYYY appear incorrectly or are swapped to MM/DD/YYYY because CSV files do not store formatting, causing Excel to rely on differing local OS regional settings.
Check your computer's current regional settings and ensure you know the original date format (e.g., DD/MM/YYYY or MM/DD/YYYY) used when the CSV file was created.
Change Regional Date Settings and Format Dates in Excel
Align your Mac's regional settings with the intended date format and explicitly format the column in Excel to ensure consistency.
Because CSV files are plain text, they do not save cell formatting. Excel defaults to the system's regional settings when interpreting date strings. If a Mac is set to US format (MM/DD/YY) and the CSV contains UK dates (DD/MM/YYYY), the dates will display incorrectly.
Open System Settings on your Mac, navigate to General > Language & Region, and ensure the date format matches your CSV data (e.g., DD/MM/YYYY).
Launch Excel for Mac and open your CSV file.
Select the column containing the dates, and press Command + 1 to open the Format Cells dialog box.
Go to the 'Number' tab, select 'Date', choose your preferred date format from the list, and click 'OK'.
Use the Data Import Wizard to Specify Date Formats
Manually specify the date format of the incoming data during the import process so Excel does not guess based on OS settings.
Format Source Dates in ISO 8601 (YYYY-MM-DD)
Modify the source data to use an internationally recognized standard format to prevent cross-platform misinterpretation.
Open and Format CSV Dates Accurately Using WPS Spreadsheet
WPS Office provides a powerful and free Spreadsheet application that makes importing and formatting CSV files straightforward. With its intuitive Text Import Wizard, you can easily define date formats to avoid regional conflicts between Windows and Mac.
- 1. Open WPS Spreadsheet: Launch WPS Spreadsheet and click on the 'Data' tab in the top ribbon.
- 2. Import the CSV file: Select 'Import Data' and choose the CSV file you want to open.
- 3. Specify the delimiter: In the File Conversion dialog, specify the delimiter (such as a comma) used in your CSV.
- 4. Define the date format: Select the column containing the dates and specify the correct Date format (like DMY or MDY) in the column settings.
- 5. Complete the import: Click 'Finish' to accurately load your date values regardless of your operating system's regional settings.
Frequently Asked Questions
Why does a CSV file lose its date formatting when I save it?
A CSV (Comma-Separated Values) file is a plain text format. It only saves the raw text and commas, meaning all visual cell formatting, including specific date displays, colors, and bold text, are stripped away upon saving.
How can I prevent Excel from changing my dates to the US format?
Excel automatically applies your computer's default regional settings to interpret dates. To stop this, you can either change your operating system's regional settings to your preferred location or use the 'Data > From Text' import feature to manually specify the date format as DMY.
Is there a universal date format that works on both Mac and Windows?
Yes, the ISO 8601 standard format (YYYY-MM-DD) is internationally recognized. If you format your dates this way in a CSV, both Windows and Mac versions of Excel (as well as other spreadsheet tools) will interpret and display them consistently without ambiguity.
Can I force a CSV to keep my date format without changing system settings?
While you cannot save formatting in the CSV itself, you can force the spreadsheet software to read dates as text. In the source data, prepend an apostrophe (') before the date, or enclose the date in quotes if generating the CSV programmatically.
