Remove T00:00:00 from ISO Dates in Excel
Question details
Convert imported ISO timestamp text into usable Excel dates without repeating Find and Replace every day.

- 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.
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.
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.
Insert a column next to the imported timestamps. If the first timestamp is in A2, select B2.
In B2, enter =DATE(--LEFT(A2,4),--MID(A2,6,2),--MID(A2,9,2)) and press Enter.
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.

Automate recurring date conversion with Power Query
Use a query when the imported data comes from a source that Power Query can refresh.
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. Open the spreadsheet: Open the imported Excel file in WPS Spreadsheets and locate the timestamp column.
- 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. Fill the column: Drag the fill handle from B2 down alongside the imported values.
- 4. Format the dates: Select the formula results, open Format Cells, and choose a Date format.

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.




