I recently needed to create a comprehensive report out of some exported log data. The system I was working with exported timestamps in the UTC format of YYYY-MM-DDTHH:MM:SSZ. For example, 2023-10-08-T13:43:30Z would be 1:43:30 PM on October 8th, 2023, in my time zone.

I figured it would be easy to change in Excel. I assumed I could do it with a custom date map on the row that the time data was in. It wasn’t that simple. I could covert the digits to something resembling local time, but I wasn’t able to remove the T and Z from the string by formatting cells.

I read a post on Exceljet about the basics of how Excel handles time in general. From that and a little more research, I derived a formula to convert the timestamps to local time.

=DATEVALUE(LEFT(F2,10))+TIMEVALUE(MID(F2,12,8)-TIME(5,0,0)

The “LEFT” and “MID” are trimming the T and Z, the DATE and TIME value functions convert the remaining digits to an Excel time serial number. The -TIME statement changes the UTC to my local time zone. Alter F2 to the cell that contains your timestamp data. Set the cell (row) format to Time or Date depending on your needs. Since the data has been converted to a time serial, any of the various time/date formats should work now.