• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Converting UTC time to EST

rdepuydt

New Member
I have a field that has a date/time in it that is in UTC format and I need it to be in EST. I've attached the file. I have pulled out the
UTC time into column H from column J. My end goal is to be able to subtract that time from the time in column B.
 

Attachments

Peter Bartholomew

Well-Known Member
In terms of your end goal, I would suggest you subtract the UTC value from the datetime in column A to avoid problems with time intervals that cross midnight. Because your text extracted from 'details' is so close to a recognised format, I would suggest a minor edit to replace the "T" and then allow the calculation to coerce the text to a datetime.
Code:
= LET(
  UTCZ, SUBSTITUTE(UTC, "T", " "),
  Date + 5/24 - UTCZ)
Without LET the formula would read
Code:
= Date + 5/24 - SUBSTITUTE(UTC, "T", " ")
 

p45cal

Well-Known Member
I'm getting time differences of 3 minutes and down to just a few seconds; is that in the right ball park?
76596
 
Last edited:
Top