Data Import & Export

How to Remove T and Z from ISO Date and Time Text in Excel

Camila Milosovich Sep 25, 2026 870 views

Question details

The user needs to convert ISO 8601 date and time text strings containing the letters 'T' and 'Z' into proper date and time values.

How to Remove T and Z from ISO Date and Time Text in Excel

Product: Excel
Device & OS: not provided
Scenario: Importing or processing data from an external source or API that formats timestamps using the ISO standard (e.g., 2024-10-04T05:19:00Z).
Observed behavior: Excel treats the ISO timestamp as a plain text string rather than a recognized date and time format, preventing the user from performing date-based calculations or sorting.

Before you start

Ensure that the cells where you plan to output the converted dates are wide enough to display the full date and time format, otherwise Excel may display hash marks (####).

Solution 1Recommended

Convert ISO Date Text Using Extracting Formulas

Use a combination of TEXT, DATE, MID, and TIMEVALUE functions to precisely extract the numeric values and convert them into a readable format.

This formula method is highly reliable as it isolates specific characters from the text string regardless of external formatting factors. It pulls the year, month, day, and time components piece by piece.

Select an output cell

Click on an empty cell next to your target data (for example, select cell B1 if your ISO text is in A1).

Enter the conversion formula

Type the following formula: =TEXT(DATE(MID(A1,1,4),MID(A1,6,2),MID(A1,9,2))+TIMEVALUE(MID(A1,12,8)),"mm/dd/yyyy hh:mm:ss") and press Enter.

Apply to multiple cells

Click the converted cell and drag the fill handle (the small square at the bottom-right corner) down to apply the formula to the rest of your data.

Convert ISO Date Text Using Extracting Formulas

Format Customization: You can modify the 'mm/dd/yyyy hh:mm:ss' portion of the formula to match your regional date preferences, such as 'dd/mm/yyyy hh:mm:ss'.

Solution 2

Clean Data Using Find and Replace

A quick manual method to clean text strings by directly replacing the T and Z characters within the existing cells.

Solution 3

Transform Data via Power Query

Best for handling large datasets or recurring data imports where dates need to be cleaned automatically on refresh.

WPS Office Data Tools

Clean and Convert Date Formats Easily with WPS Spreadsheet

WPS Spreadsheet provides powerful data processing capabilities that are perfectly compatible with Microsoft Excel formulas and shortcuts. You can seamlessly convert ISO date formats using the exact same text functions or batch-process them with the Find and Replace tool.

1. Open your data file: Launch WPS Spreadsheet and open the workbook containing your ISO date texts.
2. Select the data: Highlight the column or cells containing the dates that need conversion.
3. Use Find and Replace: Press Ctrl+H to open Find and Replace. Replace 'T' with a single space, and then replace 'Z' with nothing.
4. Format as Date: Right-click the selected cells, choose 'Format Cells', and select your preferred Date and Time display layout.

100% compatible with Microsoft Excel formulas like DATE, MID, and TEXTSupports rapid batch processing with the built-in Find and Replace toolLightweight application with fast handling for large datasetsFree to use for daily spreadsheet tasks and data formatting

Frequently Asked Questions

Why does Excel add T and Z to my imported dates?

Excel doesn't add these letters. The T and Z are standard characters in the ISO 8601 date and time format, which is frequently used when exporting data from databases or web APIs. The 'T' separates the date from the time, and the 'Z' stands for Zulu time (UTC).

Can I convert the text to local time after removing the Z?

Yes. Once you successfully convert the text to a standard date/time serial number using the formula method, you can add or subtract hours. For example, to adjust a UTC time backward by 5 hours, you would add -(5/24) to the converted cell.

Will the DATEVALUE function work directly on ISO strings?

No, the DATEVALUE function alone typically cannot parse a full ISO string containing T and Z natively. It requires text manipulation functions like MID or the Find and Replace method to isolate just the date portion before it can properly recognize it.

Excel & Data Problems