To get an actual Date value which you can format using normal number formatting…
=DATEVALUE(MID(A1,1,10)) + TIMEVALUE(MID(A1,12,8))
eg.
| A | B | |
|---|---|---|
| 1 | 2016-02-22T05:03:21Z | 2/22/16 5:03:21 AM |
- Assumes timestamps are in UTC
- Ignores milliseconds (though you could add easily enough)
The DATEVALUE() function turns a formatted date string into a value, and TIMEVALUE() does the same for times. In most spreadsheets dates & times are represented by a number where the integer part is days since 1 Jan 1900 and the decimal part is the time as a fraction of the day. For example, 11 June 2009 17:30 is about 39975.72917.
The above formula parses the date part and the time part separately, then adds them together.