Forum Discussion
Formula for Dynamic DST Time field
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.
- =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
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?
- =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
