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?

Improve this question
1
  • As help-info.de hinted at, EST is used during standard time. October 28, 2022 was during daylight saving time in the US, so it would be confusing to use EST on that particular date.
    – davidmneedham
    Commented Apr 4, 2023 at 16:16

1 Answer 1

Reset to default
3

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.

Improve this answer
2
  • Thank you! Worked perfectly ! I knew I was close, but wasn't sure how to get there!
    – Sheri Morgan
    Commented Apr 4, 2023 at 16:14
  • @Sheri Morgan - you're welcome. If an answer has helped you solve your problem feel free to mark the answer as accepted. You should also consider voting it up.
    – help-info.de
    Commented Apr 4, 2023 at 16:16

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .