Convert UT time to PST

[mun_kut]

Hi

I am trying to convert Universal Time in "Text" format to PST in Date & Time format. Is there a smart way of doing it?

Column A has text value "20090118170152UT" and this should be converted to a date value of 01/18/2009 09:01:52 in Column B.

Thanks for your help.

MK

 

[VoG]

Try

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2),MID(A1,13,2))

 

[Jonmo1]

If that is a consistent format...Try

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TEXT(MID(A1,9,6),"00\:00\:00")

Format the formula cell as custom
yyyy/mm/dd hh:mm:ss

Hope that helps.

 

[mun_kut]

Wonderful. But this is still showing the GMT time...the time should be converted to PST.

 

[VoG]

Try

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2),MID(A1,13,2))-TIME(7,0,0)

 

[Jonmo1]

OH, so we just need to subtract 8 hours...

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TEXT(MID(A1,9,6),"00\:00\:00")-"08:00:00"

 

[mun_kut]

Brilliant. It may be a problem with the settings...but I am not getting the "seconds" value in the output...

 

[VoG]

You need to format the cell as Custom

mm/dd/yyyy hh:mm:ss

 

[mun_kut]

That worked. Thanks a lot, guys!

 

[Scott Huish]

=text(left(a1,8),"00\/00\/00")+(text(right(a1,6),"00\:00\:00")-1/3)