Skip to content

How-to

How to Get Excel to Read ISO 8601 Timestamps With T and Z

Excel does not recognise 2024-06-29T13:45:07Z as a date. Convert ISO 8601 values with a Z or an offset to a form Excel reads, in UTC or in your own zone.

The problem

“My file has a thousand timestamps like 2024-06-29T13:45:07Z. Excel leaves them as text, so I cannot sort, filter or subtract them.”

New to this? Read the lesson on the Z and the +02:00 first.

Why Excel leaves them as text

ISO 8601 is the format APIs, JSON, logs and most modern exports use: 2024-06-29T13:45:07Z, or with an offset, 2024-06-29T13:45:07+02:00, often with fractions of a second (.123). Excel’s date recognition does not accept the full form. The T between date and time, the Z, and an offset are not part of what it reads as a date-time when it opens a CSV or when you type a value, so the cell stays text.

Text sorts alphabetically, which matches time order only while every value has the same offset and the same number of decimals. It cannot be filtered by date or subtracted to get a duration, and formulas that expect a date return #VALUE!.

The second problem is the zone. Z means UTC and +02:00 means two hours ahead of UTC. If you only strip those characters, rows written with different offsets end up on different clocks in one column.

Fix it on Date And Time Converter

  1. Paste one value into the converter. It detects ISO 8601, with or without fractions, Z or an offset.
  2. Choose YYYY-MM-DD HH:mm:ss as the target format. Excel recognises that layout as a date and time in every regional setting.
  3. Choose the target zone: UTC to keep one clock for all rows, or your own zone to read them as local times. Offsets in the input are applied, not dropped, and a target zone with daylight saving uses the offset of each row’s own date.
  4. For a file of timestamps, use the file workspace: same choices, applied to the whole column, with the other columns untouched.
Worked example: The T and the Z removed: the same UTC time, in a form Excel reads as a date.
Input
2024-06-29T13:45:07Z read as YYYY-MM-DDTHH:mm:ss.SSSZ (detected)
Result
2024-06-29 13:45:07 YYYY-MM-DD HH:mm:ss · YYYY-MM-DD HH:mm:ss

Open in the converter: 2024-06-29T13:45:07Z → 2024-06-29 13:45:07

Worked example: An offset is applied, not dropped: 13:45 two hours ahead of UTC is 11:45 UTC.
Input
2024-06-29T13:45:07+02:00 read as YYYY-MM-DDTHH:mm:ss.SSSZ (detected)
Result
2024-06-29 11:45:07 YYYY-MM-DD HH:mm:ss · YYYY-MM-DD HH:mm:ss

Open in the converter: 2024-06-29T13:45:07+02:00 → 2024-06-29 11:45:07

A whole column?

Open the CSV in the file workspace: pick the column, the formats and the zones, and the first 8 rows are converted at once, with counts for the whole file. The file is read in your browser and never uploaded. Downloading the converted file is part of Pro ($9.99/month).

Open the file workspace

In Excel or code

A formula for values ending in Z

With the timestamp in A2, this returns the UTC date and time as a real Excel value (format the cell as yyyy-mm-dd hh:mm:ss); it also works in Google Sheets:

=DATEVALUE(LEFT(A2,10)) + TIMEVALUE(MID(A2,12,8))

Values with an offset

For values of the exact form 2024-06-29T13:45:07+02:00 (no fractions), subtract the offset to get UTC:

=DATEVALUE(LEFT(A2,10)) + TIMEVALUE(MID(A2,12,8))
  - (MID(A2,20,1)&"1") * TIMEVALUE(MID(A2,21,5))

Any other layout, such as fractions of a second before the offset, moves the characters and breaks the positions. That fragility is the reason to convert the column instead.

Power Query

Load the file with Data › From Text/CSV › Transform Data and set the column’s type to Date/Time/Timezone. Power Query reads the Z and the offsets. A custom column with DateTimeZone.ToUtc([ts]) then puts every row on UTC (ts being the column’s name).

Code

# Python 3.11+: fromisoformat accepts Z
from datetime import datetime, timezone
datetime.fromisoformat("2024-06-29T13:45:07Z").astimezone(timezone.utc)

# pandas: a whole column, with offsets, on UTC
pd.to_datetime(df["ts"], utc=True).dt.strftime("%Y-%m-%d %H:%M:%S")

Pitfalls

  • Deleting the offset. Replacing T with a space and cutting at 19 characters works only when every row ends in Z.
  • Fractions of a second. Excel keeps milliseconds, but its default formats hide them. Use hh:mm:ss.000 in the cell format to see them.
  • Saving back to CSV. Excel writes the converted values in your regional format, not ISO 8601. If another program needs ISO 8601 again, export from the workspace, not from Excel.

Questions

What does the Z at the end mean?

Z means the time is in UTC (it is read “Zulu”). It is the same as writing +00:00.

Can I just delete the T and the Z?

Only when every value ends in Z. A value with an offset such as +02:00 is two hours ahead of UTC; deleting the offset without applying it puts that row two hours out.

Will the result be in my time zone?

Only if you ask for it. Choose your zone as the target and each value is converted with the offset in force on its own date, daylight saving included.