Converting UTC time to EST

[rdepuydt]

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.

 

[Peter Bartholomew]

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.

= LET(
  UTCZ, SUBSTITUTE(UTC, "T", " "),
  Date + 5/24 - UTCZ)

Without LET the formula would read

= Date + 5/24 - SUBSTITUTE(UTC, "T", " ")

 

[p45cal]

I'm getting time differences of 3 minutes and down to just a few seconds; is that in the right ball park?