Convert Numeric Dates in a CSV File Using Excel Text to Columns
Question details
Convert numeric date values from a CSV file into correctly displayed Excel dates.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A CSV date column needs conversion, but the usual delimiter-based Text to Columns method does not produce the expected date format.
- Observed behavior
- Excel does not apply the expected date format to the imported values.
Check the original CSV to identify the date order, such as DMY or YMD, and whether the values contain leading zeros. If Excel has already changed or removed important characters, import the column as text using Data > From Text/CSV before converting it.
Convert compact numeric dates with Fixed Width
Use this method for compact date values such as 31012024 when you know their day, month, and year order.
In Excel, select the cells or column containing the numeric date values.
Choose Data > Text to Columns, select Fixed Width, and click Next.
Check the preview for column break lines and remove any that split a date value. Click Next.
Under Column data format, select Date and choose the order that matches the source values, such as DMY for 31012024. Click Finish.
Select the converted cells, press Ctrl+1, and choose a format under Date. Save as an Excel workbook if you need to retain that formatting.

Format existing Excel date serial numbers
Use this method if the numeric values are already Excel date serial numbers rather than compact dates.
Convert Numeric CSV Dates in WPS Spreadsheets
Use a formula in WPS Spreadsheets to turn compact DMY values into dates, then apply a date display format and save an XLSX copy.
- 1. Open the CSV: Open the CSV in WPS Spreadsheets and locate the column containing the date values.
- 2. Check the source order: Confirm that the values use an eight-digit DMY pattern, such as 31012024. If needed, use an empty column next to the source dates for the results.
- 3. Enter the conversion formula: If the first source value is in A2, enter =DATE(RIGHT(TEXT(A2,"00000000"),4),MID(TEXT(A2,"00000000"),3,2),LEFT(TEXT(A2,"00000000"),2)) in an empty cell. Adjust A2 to match your source cell.
- 4. Fill and format the dates: Drag the formula cell's fill handle down the column. Select the results, open Format Cells, and choose a Date format.
- 5. Save the formatted workbook: Choose File > Save As and select an XLSX workbook to retain the date formatting.

Frequently Asked Questions
Why did Excel display my CSV dates differently when I opened the file?
CSV files store values without cell formatting, so Excel applies its own formats when opening them. Use Data > From Text/CSV to control the import, or convert the column afterward.
Which date order should I select in Text to Columns?
Select the order used in the source values. For example, choose DMY for 31012024 or YMD for 20240131. Check the original CSV if the order is ambiguous.
What if a leading zero disappeared from a date?
Reimport the original file using Data > From Text/CSV and set the date column to Text before loading it. Then convert the values using the correct date order.
Why did my date formatting disappear after saving as CSV?
CSV does not store spreadsheet cell formats. Choose File > Save As and save an XLSX copy to retain the date display format.
Can I simply format every numeric date as a date?
Only if the numbers are already Excel date serial values. Compact dates such as 31012024 need conversion first; applying a date format alone will not interpret their day, month, and year correctly.




