Good day, folks:
I just registered an account to seek help on this. I have created a formula to convert UTC timestamps to US Eastern Time Zone with DST conditions. I would paste it here, but I keep getting an error despite using the wrap formula tag. (Please see the attached workbook.)
It appeared to work just fine at first; however, after inspecting the results, I am finding that some dates are not converting properly, as the "IF AND" criteria #3 is indicating a value of False. I have attached an example workbook to demonstrate the issue.
The formula is supposed to check the following:
1. If the target cell is empty, make the resulting cell empty.
2. If the target cell is not empty, check whether the following three conditions are true:
a. The date occurs on or after the 2nd Sunday of March.
b. The date occurs before the 1st Sunday of November.
c. The time hour value is greater than or equal to 7 (i.e. 07:00:00 AM).
3. If the three above conditions are TRUE, subtract 4 hours from the target cell (converts the timestamp to Eastern Daylight Time, UTC-4)
4. If any of the three above conditions are FALSE, subtract 5 hours from the target cell (converts the timestamp to Eastern Standard Time, UTC-5).
I have been toiling with this function for a long while and I have been unsuccessful finding a solution. Your help would be greatly appreciated.
Thank you!
Bookmarks