0

A system I am using provides UTC time in the following format. 20190802145655UT

What Excel formula could I use to convert this to a timestamp 8-2-19 7:56:55 AM? I've done a bunch of searches, but the format the time is provided in is giving me a hard time. (PDT should have been -7 hours that day.)

Thanks for any help in advance!

MarkSFO
  • 5
  • 1
  • 3

3 Answers3

3
=TEXT(LEFT(A1,14),"0000-00-00 00\:00\:00")-7/24

and format as "m-d-yy h:mm:ss AM/PM"

enter image description here

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • Ron, Thank you so much! That worked! Much appreciated. – MarkSFO Nov 19 '20 at 01:24
  • @MarkSFO You got yourself a brilliant answer - better than the one I would have given had you told me the task as it really is. Please make sure to mark the answer as *Selected*. (There is a checkmark on the top left that you can click). – Variatus Nov 19 '20 at 01:31
  • Done. Thank you both. – MarkSFO Nov 19 '20 at 01:34
0

This formula will convert your UT string in cell A2 to a proper date/time value.

=VALUE(MID(A2,19,18))

This could be made more complicated by searching for the opening parenthesis but I believe your format is very rigid, with the parenthesis always in 18th position. Therefore it can be hard-coded.

The formula will return a number. Format the cell with a custom format like mm/dd/yyyy hh:mm:ss or any other format you prefer.

You can adjust the number as follows.

=VALUE(MID(A2,19,18))+(-7/24)

In this example 7 hours are being deducted from the given time. If you need more flexibility you can replace the -7 with a cell reference.

Variatus
  • 14,293
  • 2
  • 14
  • 30
  • @MarkSFO Thank you for all the fun you are giving us but Ron's is not merely the only correct answer in this thread but also a very good one. I don't know if you can still change your selection but if the system does allow it, please do mark his answer rather than mine. – Variatus Nov 19 '20 at 02:18
0

I tried the formulas provided and neither worked for me.

I used this and it seems to work... =C7-7/24
C7 is first cell with UTC to convert

I used the following format, custom mm/dd/yyyy h:mm AM/PM

UTC converted to PST time: enter image description here

Reza Rahemtola
  • 1,182
  • 7
  • 16
  • 30
IvanM
  • 1