How to parse string timestamp into date

Question

I have a text value that looks like this: 2019-03-25T06:05:00-07:00. The general format is yyyy-mm-ddThh:mm:ss-GMT. I don't care about the GMT part. I am trying to use this text field to make time series scatter plots in excel.

I want to convert it to a timestamp as simply as possible. I currently do this using a bunch of formulas:

Input: 2019-03-25T06:05:00-07:00

1. Extract parts of time individually: =value(mid(input_cell,12,2))
2. Use date() and time() to get timestamp types
3. Add them together per this answer: https://stackoverflow.com/a/41164517/11163122
4. Use custom formatting to get a timestamp value

Output: 3/25/2019 6:05:00 AM

In total this took me 8 cells and custom formatting. This is too complicated. What is a simpler/more elegant way to do this?

Answer 1

You can use:

=--REPLACE(LEFT(A1,19),11,1," ")

and format as desired

Answer 2

Turns out the timestamp type is ISO 8601: https://en.wikipedia.org/wiki/ISO_8601#Combined_date_and_time_representations

This led me to this answer: https://stackoverflow.com/a/26315881/11163122

Using the formula there, I found this to be a sufficient solution. If anyone out there has a better method, please speak up! :)