3

I have a UTC timestamp that looks like this in Excel:

2022-10-28T18:12:23.603Z

I need the information to appear as 10/28/22 hh:mm:ss AM/PM format but I need it to come out in EST timezone. Right now, it is displaying as 10/28/22 6:12 PM and it should be 1:12 PM.

I am using =DATEVALUE(LEFT(P2,10))+TIMEVALUE(MID(P2,12,8)) now.

What do I need to do to make the formula calculate EST timezone? Basically subtract 5 hours?

help-info.de
  • 2,159
Sheri Morgan
  • 31

1 Answers1

4

Insert a simple formula to convert UTC to EST in Excel. You'll need to subtract 5 hours from the UTC to get the EST. Divide 5 by 24 first, then subtract it from the UTC.

Please note:

EST is 5 hours behind UTC (Universal Coordinated Time). It is not adjusted for daylight saving time. When it is adjusted for daylight saving time, it becomes 4 hours behind UTC known as EDT (Eastern Daylight Time).

Solution for EST only:

=DATEVALUE(LEFT(A2,10))+TIMEVALUE(MID(A2,12,8))-5/24

like shown in the screenshot below:

enter image description here

You know, the result of the displayed time accuracy depends on your formatting.

help-info.de
  • 2,159