Working with Date and Time Issues

Try:

Formula:

=N("date")+LEFT(A3,10)+0+N("time")+MID(A3,12,8)+0+N("seven hours")+TIME(7,0,0)

Formula:

=LEFT(A3,10)+0+MID(A3,12,8)+0+TIME(7,0,0)

Because this seems to be becoming a standardized time stamp, I'm hopeful that MSFT will see fit to program Excel so it will automatically recognize this kind of time stamp in some future version. Until then, Excel will recognize this kind of time stamp if you can get rid of the T and Z characters. I would:
1) Import the text to Excel.
2) Find/Replace T with space, Z with comma (or other convenient delimiter character).
3) Text to columns -> delimited (by comma or whatever character you chose in step 2) -> At the appropriate step, make sure that Excel knows that this is YMD date data -> finish text import wizard.

Excel should correctly recognize the data/time stamp and convert to a number (formatted in whatever default date/time format Excel chooses). That will leave your UTC date/time together in the same cell. Then you can add a helper column to convert to your desired time zone by adding or subtracting the appropriate value =UTCtime-TIME(7,0,0).

Here is an alternative with Power Query to put in a standard Date Time US format.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Replaced Value" = Table.ReplaceValue(Source,"T"," ",Replacer.ReplaceText,{"Date"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Z","",Replacer.ReplaceText,{"Date"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value1",{{"Date", type datetime}, {"STG", type number}}),
    #"Trimmed Text1" = Table.TransformColumns(Table.TransformColumnTypes(#"Changed Type", {{"Date", type text}}, "en-US"),{{"Date", Text.Trim, type text}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Trimmed Text1",{{"Date", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each [Date]+#duration(0,-7,0,0))
in
    #"Added Custom"

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

Excel 2016 (Windows) 64 bit

	A	B	C	D	E	F	G	H
1	Montrezuma Pile
2	Date	STG	STG-Unit		Date	STG	STG-Unit	Custom
3	2021-12-06T23:30:00Z	105.592	ft		12/6/2021 23:30	105.592	ft	12/6/21 4:30 PM
4	2021-12-06T23:40:00Z	105.635	ft		12/6/2021 23:40	105.635	ft	12/6/21 4:40 PM
5	2021-12-06T23:50:00Z	6.448	ft		12/6/2021 23:50	6.448	ft	12/6/21 4:50 PM
6	2021-12-07T00:00:00Z	6.475	ft		12/7/2021 0:00	6.475	ft	12/6/21 5:00 PM
7	2021-12-07T00:10:00Z	6.491	ft		12/7/2021 0:10	6.491	ft	12/6/21 5:10 PM
8	2021-12-07T00:20:00Z	6.502	ft		12/7/2021 0:20	6.502	ft	12/6/21 5:20 PM
9	2021-12-07T00:30:00Z	6.492	ft		12/7/2021 0:30	6.492	ft	12/6/21 5:30 PM
10	2021-12-07T00:40:00Z	6.469	ft		12/7/2021 0:40	6.469	ft	12/6/21 5:40 PM
11	2021-12-07T00:50:00Z	6.433	ft		12/7/2021 0:50	6.433	ft	12/6/21 5:50 PM
12	2021-12-07T01:00:00Z	6.36	ft		12/7/2021 1:00	6.36	ft	12/6/21 6:00 PM
13	2021-12-07T01:10:00Z	6.263	ft		12/7/2021 1:10	6.263	ft	12/6/21 6:10 PM
14	2021-12-07T01:20:00Z	6.139	ft		12/7/2021 1:20	6.139	ft	12/6/21 6:20 PM
15	2021-12-07T01:30:00Z	5.982	ft		12/7/2021 1:30	5.982	ft	12/6/21 6:30 PM
16	2021-12-07T01:40:00Z	5.819	ft		12/7/2021 1:40	5.819	ft	12/6/21 6:40 PM
17	2021-12-07T01:50:00Z	5.623	ft		12/7/2021 1:50	5.623	ft	12/6/21 6:50 PM
18	2021-12-07T02:00:00Z	5.412	ft		12/7/2021 2:00	5.412	ft	12/6/21 7:00 PM
19	2021-12-07T02:10:00Z	5.206	ft		12/7/2021 2:10	5.206	ft	12/6/21 7:10 PM
20	2021-12-07T02:20:00Z	5.057	ft		12/7/2021 2:20	5.057	ft	12/6/21 7:20 PM

Sheet: fts-data_2019-10-23T09_21_00.00

Following Alan's description, you can also try:

Formula:

=SUBSTITUTE(SUBSTITUTE(A6,"Z",""),"T"," ")+0

formatted as

Formula:

yyyy-mm-dd hh:mm:ss

Are you still using Excel 2016?

If you have Excel 365 now, try this copied down:

=IFERROR(--TEXTSPLIT(A3,{"T","Z"}),"")

For Excel 2016:

=--LEFT(A3,FIND("T",A3)-1)

and:

=--MID(A3,FIND("T",A3)+1,8)

Copy down.

You can just take the individual elements, given they are fixed length.

Date:

Formula:

=LEFT(A3,10)+0

Time:

Formula:

=MID(A3,12,8)+0

Seven hours:

Formula:

=TIME(7,0,0)

Updated PQ Mcode to show split of Date and Time

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Replaced Value" = Table.ReplaceValue(Source,"T"," ",Replacer.ReplaceText,{"Date"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Z","",Replacer.ReplaceText,{"Date"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value1",{{"Date", type datetime}, {"STG", type number}}),
    #"Trimmed Text1" = Table.TransformColumns(Table.TransformColumnTypes(#"Changed Type", {{"Date", type text}}, "en-US"),{{"Date", Text.Trim, type text}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Trimmed Text1",{{"Date", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each [Date]+#duration(0,-7,0,0)),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Added Custom", {{"Custom", type text}}, "en-US"), "Custom", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Custom.1", "Custom.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Custom.1", "New Date"}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Renamed Columns",{{"Custom.2", "New time"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns1",{{"New Date", type date}, {"New time", type time}})
in
    #"Changed Type2"