Data Import & Export

Remove T00:00:00 from ISO Dates in Excel

Phi Hung VoPhi Hung Vo Nov 22, 2026 868 views

Question details

Convert imported ISO timestamp text into usable Excel dates without repeating Find and Replace every day.

Remove T00:00:00 from ISO Dates in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
Dates are imported regularly through Zapier in a format such as 2017-06-06T00:00:00.
Observed behavior
The imported values include a T-separated time portion that the user wants to remove while retaining usable dates.
Before you start

Check that the imported values are text and begin with a date in YYYY-MM-DD format. Identify the date column and whether your recurring import updates a table or another data source.

Solution 1Recommended

Convert the date portion with an Excel formula

Use a helper column to turn each imported timestamp into a real date without Find and Replace.

1
Add a helper column

Insert a column next to the imported timestamps. If the first timestamp is in A2, select B2.

2
Enter the formula

In B2, enter =DATE(--LEFT(A2,4),--MID(A2,6,2),--MID(A2,9,2)) and press Enter.

3
Fill and format the results

Drag the fill handle down the column, then select the results and use Home > Number Format > Short Date. If the import is an Excel table, a calculated column can fill the formula for new rows.

Convert the date portion with an Excel formula
Date-only result: This formula discards the time portion. It assumes each text value starts with a valid YYYY-MM-DD date.
Convert imported dates in WPS

Convert ISO timestamps to dates in WPS Spreadsheets

Use a formula in WPS Spreadsheets to extract the year, month, and day from imported ISO timestamp text, then format the result as a date.

  1. 1. Open the spreadsheet: Open the imported Excel file in WPS Spreadsheets and locate the timestamp column.
  2. 2. Enter the conversion formula: If the first timestamp is in A2, select B2 and enter =DATE(--LEFT(A2,4),--MID(A2,6,2),--MID(A2,9,2)).
  3. 3. Fill the column: Drag the fill handle from B2 down alongside the imported values.
  4. 4. Format the dates: Select the formula results, open Format Cells, and choose a Date format.
Convert T-separated timestamps without daily Find and ReplaceOpen and save Microsoft Excel XLSX filesUse familiar spreadsheet formulas and date formattingWork with a free, easy-to-use office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does changing the cell format leave the T00:00:00 text unchanged?

A date format changes the display of an Excel date value, not the contents of imported text. Convert the text with a formula first, then apply a date format.

Can I use DATEVALUE to convert the date portion?

Yes. For a timestamp in A2, try =DATEVALUE(LEFT(A2,10)), then apply a date format. DATEVALUE depends on Excel recognizing the extracted text under your regional settings.

How can I keep the time instead of discarding it?

For compatible regional settings, try =--SUBSTITUTE(SUBSTITUTE(A2,"T"," "),"Z","") and apply a date and time format. Removing Z does not convert the value from UTC to another time zone.

Will the formula work automatically for new imported rows?

It can if the formula is in an Excel table's calculated column and the import adds rows to that table. Otherwise, fill the formula down to the new rows or use a refreshable Power Query.