# How to convert an ISO 8601 timestamp to a date and time in Excel

Convert the date and time parts and add them: `=DATEVALUE(LEFT(A2,10))+TIMEVALUE(MID(A2,12,8))`.

**Excel:** `=DATEVALUE(LEFT(A2,10))+TIMEVALUE(MID(A2,12,8))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Timestamp (text) | Date and time |
| 2026-03-05T14:30:00Z |  |
| 2026-03-05T09:05:12Z |  |
| 2026-11-01T00:00:00Z |  |
| 2026-12-31T23:59:59Z |  |
| 2027-01-15T07:45:00Z |  |

Formula in B2, filled to B6: `=DATEVALUE(LEFT(A2,10))+TIMEVALUE(MID(A2,12,8))`

Result: 3/5/2026 14:30:00; 3/5/2026 9:05:12; 11/1/2026 0:00:00; 12/31/2026 23:59:59; 1/15/2027 7:45:00

## How it works

An ISO 8601 timestamp is text: the date in the first 10 characters, a T, then the time. `DATEVALUE(LEFT(A2,10))` gives the date, `TIMEVALUE(MID(A2,12,8))` gives the time as a fraction of a day, and adding them gives one date-time value. The trailing Z means UTC.

## Other ways

- `=--SUBSTITUTE(LEFT(A2,19),"T"," ")`: Replaces the T with a space and converts the text in one step.
- `=DATEVALUE(LEFT(A2,10))`: The date only. (Drops the time.)

## Common mistakes

- A timestamp with an offset like +02:00 is local time. Subtract the offset (add it for a minus sign) to get UTC.
- Fractional seconds like 14:30:00.123Z make the text longer; `MID(A2,12,8)` ignores them.

## Questions

**How do I convert it to my time zone?**

Add or subtract hours/24: `=B2-5/24` for US Eastern Standard Time.

**How do I write a date as ISO 8601 text?**

`=TEXT(B2,"yyyy-mm-dd")&"T"&TEXT(B2,"hh:mm:ss")&"Z"`.

**Why do I see a number like 46086.60417?**

That is the date-time value. Format the cell as a date and time, for example m/d/yyyy h:mm.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/convert-an-iso-8601-timestamp