We're having to adjust UTC to local time for historical data so I needed a way to determine if the date is DST or not.
DST starts on the 2nd Sunday in March and ends on the first Sunday in November.
I tried this out with 2016, 2017 and 2018. Seems to hold but will likely create a load script to verify.
Also, DST changed back in 2007 so this only holds 2007 going forward.
//Start of DST for current year
DATE(MakeDate(YEAR(NOW()),3,1)+(6-WEEKDAY(MakeDate(YEAR(NOW()),3,1)))+7) & chr(13) &
//End of DST for current year
DATE(MakeDate(YEAR(NOW()),11,1)+(6-WEEKDAY(MakeDate(YEAR(NOW()),11,1))))
//Start of DST for historical date
DATE(MakeDate(YEAR([Field]),3,1)+(6-WEEKDAY(MakeDate(YEAR([Field]),3,1)))+7) & chr(13) &
//End of DST for current year
DATE(MakeDate(YEAR([Field]),11,1)+(6-WEEKDAY(MakeDate(YEAR([Field]),11,1))))
This is not perfect since some states do not observe dst so feel free to post modifications or maybe there is a simpler method.
