Power Query Problems

How to Convert an ISO Date and Time to a Simple Date in Excel

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to convert ISO 8601 date strings into valid, formattable dates within the spreadsheet.

How to Convert Date and Time to Text in Excel and WPS Spreadsheet

Product: Excel
Device & OS: not provided
Scenario: Working with imported or exported data containing standardized ISO timestamps that do not automatically format as dates.
Observed behavior: The spreadsheet treats values like '2023-09-28T02:01:29.087Z' as plain text, meaning standard cell date formatting has no effect on the displayed value.

Before you start

Ensure your target cell has enough space and temporarily insert a new blank column to hold your converted date values before replacing the original text data.

Solution 1Recommended

Extract Date and Time Using Built-in Excel Formulas

Use string manipulation formulas like LEFT and MID combined with the DATE function to parse the text into a recognized date value.

Because the ISO format is highly consistent, you can reliably extract the year, month, and day using character positions.

Extract just the date

Click an empty cell (e.g., B2) next to your ISO text (in A2) and enter `=DATE(LEFT(A2,4),MID(A2,6,2),MID(A2,9,2))`. Press Enter.

Extract both date and time

If you need to keep the exact time, use `=DATE(LEFT(A2,4),MID(A2,6,2),MID(A2,9,2))+MID(A2,12,12)` instead.

Apply cell formatting

Right-click the newly calculated cell, select 'Format Cells', and apply a custom date format such as `mm/dd/yyyy` or `mm/dd/yyyy hh:mm:ss.000 AM/PM`.

Solution 2

Transform Data Formatting Using Power Query

Use Power Query to batch convert the ISO text into a date format. This method is ideal for processing large datasets imported from external sources.

Efficient Spreadsheet Tool

Effortlessly Manage Data Conversions in WPS Spreadsheet

WPS Office offers a robust spreadsheet tool that flawlessly executes advanced formulas for text-to-date conversions, giving you a smooth and familiar experience.

1. Open your data file: Launch WPS Spreadsheet and open the document containing your ISO date strings.
2. Apply the extraction formula: In an adjacent column, type `=DATE(LEFT(A2,4),MID(A2,6,2),MID(A2,9,2))` to quickly parse the text into a real date.
3. Format the output: Right-click the cell, select 'Format Cells', and choose your preferred local date format.

Fully compatible with Microsoft Excel (.xlsx, .csv) formatsSupports DATE, LEFT, MID, and other complex data manipulation formulas seamlesslyLightweight application with a highly intuitive user interfaceFree to download and use across Windows, Mac, Linux, iOS, and Android

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel treat my ISO date as text?

Excel has specific regional settings for recognizing dates. Because the ISO 8601 format (containing 'T' and 'Z') often doesn't match default local Windows date formats, Excel defaults to treating the unrecognized string as standard text.

Can I use 'Text to Columns' to fix ISO dates?

Yes, you can use the 'Text to Columns' feature under the Data tab. By setting the delimiters to 'T' or using fixed width, you can split the date and time components into separate columns that Excel can more easily recognize.

Will this formula work if the text string has a different length?

The formulas using LEFT and MID rely on the strict, fixed-character structure of an ISO date string (YYYY-MM-DD). If your data's structure varies in length, you may need to use Power Query or a combination of SEARCH and FIND functions instead.

Excel & Data Problems