UTC to PST one cell to one cell

Tdaddy4

New Member
Joined
Nov 7, 2019
Messages
6
Office Version
  1. 365
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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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
AB
12017-08-11T06:15:19.711Z8/10/17 10:15 PM
Sheet1
Cell Formulas
RangeFormula
B1=DATEVALUE(LEFT(A1,10))+TIMEVALUE(MID(A1,12,8))-8/24


Format the B1 cell as a date/time.
 
Last edited:
Upvote 0
Also:
=SUBSTITUTE(LEFT(A1,19),"T"," ")-1/3


Excel 2010
ABC
12017-08-11T06:15:19.711Z8/10/2017 10:15 PM
Sheet1
Cell Formulas
RangeFormula
C1=SUBSTITUTE(LEFT(A1,19),"T"," ")-1/3



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

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top