Working with Date and Time Issues

Try:

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

formatted as

Formula:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

Time:

Formula:

Please Login or Register  to view this content.

Seven hours:

Formula:

Please Login or Register  to view this content.

Updated PQ Mcode to show split of Date and Time

Please Login or Register  to view this content.