Data Import & Export

How to Keep Custom Date Formats When Saving Excel as CSV

Maira Mehtab Sep 27, 2026 869 views

Question details

The user needs to maintain specific custom date formats (such as d.m.) when exporting spreadsheet data to a CSV file.

Product: Excel
Device & OS: not provided
Scenario: Saving a spreadsheet containing custom-formatted date cells as a Comma Separated Values (CSV) file.
Observed behavior: Because CSV is a plain text format, it drops Excel cell formatting. Consequently, custom date formats are lost or revert to the system default when the saved CSV file is reopened.

Before you start

Identify the exact format code you are currently using (such as 'dd.mm.yyyy' or 'd.m.') by right-clicking your date cells and checking the Custom category in the Format Cells dialog.

Solution 1Recommended

Convert Dates to Text Using the TEXT Formula

Use the TEXT function to permanently convert your date values into static text strings that match your desired custom format before saving to CSV.

Applying a custom format via the 'Format Cells' menu only changes how the date visually appears in Excel, not the underlying data. When you save as CSV, only the raw data is kept. Converting the dates to text ensures the exact visual formatting is written into the CSV file.

Create a helper column

Insert a new blank column immediately adjacent to your existing date column.

Apply the TEXT function

In the first cell of the new column, enter the formula =TEXT(A1, "d.m."), replacing 'A1' with the reference to your original date cell and 'd.m.' with your specific date format code.

Fill the formula down

Click and drag the fill handle at the bottom right of the formula cell down to apply the conversion to all dates in your dataset.

Paste as Values

Select the newly generated text dates, press Ctrl+C to copy, right-click the same selection, and choose 'Paste as Values' (the clipboard icon with a 123) to remove the formulas.

Save as CSV

Delete the original date column if it is no longer needed, then go to File > Save As, and select 'CSV (Comma delimited)' from the format dropdown.

Verification Tip: To verify that your dates were saved correctly, open the resulting CSV file in Notepad instead of Excel. If you double-click to open it in Excel, Excel's automatic data parsing may instantly reformat your text dates back into standard system dates.

Manage Data Export Efficiently

Seamlessly Convert and Export CSV Data with WPS Office

WPS Spreadsheet provides intuitive text functions, robust Paste Special options, and an advanced Text Import Wizard, allowing you to control exactly how your dates and data are formatted during CSV exports and imports.

1. Apply the TEXT function: Open your file in WPS Spreadsheet and use the =TEXT() function to convert dynamic dates into text formats.
2. Paste as Values: Copy the formula results, right-click, and select 'Paste Special' > 'Values' to finalize the text strings.
3. Export to CSV: Click 'Menu' > 'Save As', choose 'CSV (Comma delimited) (*.csv)' from the File Type dropdown, and click Save.
4. Import Safely: When reopening, go to the 'Data' tab, select 'Import Data', and use the wizard to define your date columns strictly as 'Text'.

Fully compatible with Microsoft Excel file formats, including .xlsx, .xls, and .csv.Advanced Data Import Wizard lets you force columns to load as Text, preventing unwanted date reformatting.Built-in robust TEXT formulas for quick data type conversions.Lightweight, fast, and free alternative for comprehensive spreadsheet management.

microsoft office alternative - wps office

Frequently Asked Questions

Why do my dates revert to the wrong format when I reopen the CSV in Excel?

CSV files are strictly plain text and cannot store spreadsheet formatting rules like bolding, colors, or custom date displays. When you reopen a CSV file directly in Excel, it automatically detects date-like strings and applies your computer's default regional date format to them.

Does applying 'Format Cells' before saving to CSV work?

No. The 'Format Cells' feature only alters the visual presentation of the underlying serial date number in a spreadsheet. Saving to CSV exports the raw data without those visual rules, which is why you must physically convert the data to text strings using formulas.

How can I view my CSV without Excel changing the dates?

You can right-click the CSV file and open it with a plain text editor like Notepad. If you need to view it in a spreadsheet application, open a blank workbook first, use the Data Import Wizard to load the CSV, and specify that the column containing dates should be imported as 'Text'.

Excel & Data Problems