UTC to PST one cell to one cell

[Tdaddy4]

I am having difficulty with converting a cell with UTC time
[TABLE="width: 309"]
<tbody>[TR]
[TD="width: 309"]2017-08-11T06:15:19.711Z

to PST time[/TD]
[/TR]
</tbody>[/TABLE]


Using Office Pro Plus 2016 Excel 2016 MSO (16.0.4849.1000) 32 Bit

 

[Eric W]

Welcome to the forum.

It appears that your date/time is a text value, not a native Excel date/time format. So first you need to convert that to the native Excel format, which you can do with DATEVALUE and TIMEVALUE. Then once you have that, you can convert from UTC to PST by just subtracting the number of hours difference (8) from that value. Since the time portion of the date/time value is the decimal part, you have to convert 8 hours into what fraction of a day that is, or 8 hours divided by 24 hours. So in all, the formula looks like:

Book1
	A	B
1	2017-08-11T06:15:19.711Z	8/10/17 10:15 PM
Sheet1

Cell Formulas
Range	Formula
B1	=DATEVALUE(LEFT(A1,10))+TIMEVALUE(MID(A1,12,8))-8/24

Format the B1 cell as a date/time.

 

[Scott Huish]

Also:
=SUBSTITUTE(LEFT(A1,19),"T"," ")-1/3

Excel 2010
	A	B	C
1	2017-08-11T06:15:19.711Z		8/10/2017 10:15 PM
Sheet1

Cell Formulas
Range	Formula
C1	=SUBSTITUTE(LEFT(A1,19),"T"," ")-1/3

Format as:
m/d/yyyy h:mm AM/PM