Formula for Dynamic DST Time field

Occasional Contributor

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 Replies


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?

best response confirmed by sconway1985 (Occasional Contributor)
=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