Formula for Dynamic DST Time field

[sconway1985]

I am trying to write a formula that allows me to reference a time field which is in UCT, to translate it to local time, but also work dynamically with DST. The formula below is what I have been using, but doesn't seem to work prior to the 12th of the month.

=IF(AND(MONTH(NOW() - TIME(6,0,0)) >= 3, MONTH(NOW() - TIME(6,0,0)) <= 11),[@[REFERENCED TIME FIELD]]-5/24,[@[REFERENCED TIME FIELD]]-6/24)

Essentially below is what I am trying to accomplish.

If now is between 3/12 2:00AM and 11/5 1:59:59AM = [REFERENCED TIME FIELD]-5/24, or if now is between 11/5 2:00AM and 3/12 1:59:59AM = [REFERENCED TIME FIELD]-6/24

Is this possible with just a formula?

Thanks in advance.

[#2]

@sconway1985 

If NOW() is the 5th of November, 2:30 AM, how do you know if it is before or after the clock has been turned back?

[#3 · Accepted solution]

=IF(AND([@[Shift Start]]>=DATE(YEAR(NOW()), 3, 1) + (14 - WEEKDAY(DATE(YEAR(NOW()), 3, 1), 1))+TIME(2, 0, 0), [@[Shift End]]<DATE(YEAR(NOW()), 11, 1) + (8 - WEEKDAY(DATE(YEAR(NOW()), 11, 1), 1))+TIME(2, 0, 0)), [@[Shift Start]]-(5/24), [@[Shift Start]]-(6/24))


This is the fix