Formula to Convert Date and Time to PDT and round down on the milliseconds

[tbruce]

Hoping someone could help here. I'm trying to create a formula to convert this date and time stamp that is in UTC (zulu time) and convert it to PDT (-7)/24) and round it DOWN from the last three digits (milliseconds) if .499 or lower, and round UP if .500 and greater and get rid of the Z and then the milliseconds and show to the seconds (rounded down/up). The rounding down/up and getting rid of the Z is what I'm struggling with. I've tried several variations and nothing seems to work. When I remove the Z's I lose the milliseconds, and I need that to round down/up. It seems to Round Up automatically when I get rid of the Z with a CTRL+H and replace the Z with nothing. Any help would be much appreciated.

​	A​	B​
1	2019-09-04 02:16:07.135Z

 

[Dave Patton]

Could you post a few examples with expected results?
Do you have text or numeric data?
What format do you want for the result?
Please post an extract of your sheet with the forum's tool named XL2BB.

One quick idea is shown below

Dates Time.xlsb
	A	B
1
2	2019-09-04 02:16:07.135Z	2019:09:04 2:16:07
3	2019-09-04 02:16:07.500Z	2019:09:04 2:16:08
4
5b

Cell Formulas
Range	Formula
B2:B3	B2	=MROUND(--LEFT(A2,23),"0:00:01")

 

[Dave Patton]

and convert it to PDT (-7)/24)

Dates Time.xlsb
	A	B
7	2019-09-04 22:16:07.135Z	2019:09:04 15:16:07
8	2019-09-04 22:16:07.500Z	2019:09:04 15:16:08
9	2019-09-04 12:16:07.550Z	2019:09:04 05:16:08
10	2019-09-04 12:16:07.499Z	2019:09:04 05:16:07
5b

Cell Formulas
Range	Formula
B7:B10	B7	=MROUND(--LEFT(A7,23),"0:00:01")-7/24

 

[Dave Patton]

edit just to the formatting

Dates Time.xlsb
	A	B
7	2019-09-04 22:16:07.135Z	2019-09-04 15:16:07
8	2019-09-04 22:16:07.500Z	2019-09-04 15:16:08
9	2019-09-04 12:16:07.550Z	2019-09-04 05:16:08
10	2019-09-04 12:16:07.499Z	2019-09-04 05:16:07
5b

Cell Formulas
Range	Formula
B7:B10	B7	=MROUND(--LEFT(A7,23),"0:00:01")-7/24