UTC to PST one cell to one cell

Tdaddy4

New Member
Joined
Nov 7, 2019
Messages
1
I am having difficulty with converting a cell with UTC time
2017-08-11T06:15:19.711Z

to PST time

<tbody>
</tbody>


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

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,784
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:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="color: #333333;;">2017-08-11T06:15:19.711Z</td><td style="text-align: right;;">8/10/17 10:15 PM</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B1</th><td style="text-align:left">=DATEVALUE(<font color="Blue">LEFT(<font color="Red">A1,10</font>)</font>)+TIMEVALUE(<font color="Blue">MID(<font color="Red">A1,12,8</font>)</font>)-8/24</td></tr></tbody></table></td></tr></table><br />

Format the B1 cell as a date/time.
 
Last edited:

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,835
Also:
=SUBSTITUTE(LEFT(A1,19),"T"," ")-1/3

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">2017-08-11T06:15:19.711Z</td><td style="text-align: right;;"></td><td style="text-align: right;;">8/10/2017 10:15 PM</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C1</th><td style="text-align:left">=SUBSTITUTE(<font color="Blue">LEFT(<font color="Red">A1,19</font>),"T"," "</font>)-1/3</td></tr></tbody></table></td></tr></table><br />


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

Forum statistics

Threads
1,077,736
Messages
5,335,903
Members
399,056
Latest member
CityGirlLuv

Some videos you may like

This Week's Hot Topics

Top