If you are using processes, flows, templates to automate your business processes you have probably run into issues regarding Daylight Savings Time (DST) at some point. Winter ’20 has provided an additional setting for Locale Settings that will help provide accurate DST times for DateValue() formulas. Setting this can avoid one-hour discrepancies when processing times between 11:00 PM and 1:00 AM but there are limitations.
Another way is to use work arounds. These include checking the DateTime to be evaluated to see if they are in DST and adjust accordingly in your formulas. The other is to use Offsets in your formulas to calculate local time (e.g., 5/24 which is 5 hours from GMT time or Central Standard Time). When the DST changes, you can edit the Offset from 5 to 6 and visa versa.
How you manage Daylight Savings Time in your organization will depend on what formulas you use.
Change Company Locale Settings:
- From Set Up (Gear icon) enter Company Information into the Quick Find box.
- Under Locale Settings, select the Improve DATEVALUE() accuracy for DST check box.
NOTE: If your existing formula(s) includes workarounds that adjust the date values between 11:00 PM and 1:00 AM, remove them before enabling this setting.
Edit existing formulas with Offset values:
Let’s say you are using a formula to format the Time into a string.
TEXT(IF( OR( VALUE( MID( TEXT( {!ActivityDateTime.ActivityDateTime} – 5/24 ), 12, 2 ) ) = 0, VALUE( MID( TEXT( {!ActivityDateTime.ActivityDateTime} – 5/24 ), 12, 2 ) ) = 12 ), 12, VALUE( MID( TEXT( {!ActivityDateTime.ActivityDateTime} – 5/24 ), 12, 2 ) ) – IF( VALUE( MID( TEXT( {!ActivityDateTime.ActivityDateTime} – 5/24 ), 12, 2 ) ) < 12, 0, 12 ) )) & “:” & MID( TEXT( {!ActivityDateTime.ActivityDateTime} – 5/24 ), 15, 2 )
When DST changes you will edit the Offset values you are using (Red values). In March the values would be 5 hours difference and in November it would be 6 hours.
Evaluate Date to determine DST adjustments:
Daylight Savings Time begins on the second Sunday in March. The formula to get this date dynamically is:
DATE( YEAR(TODAY()),3,(2*7+1)) – WEEKDAY(DATE(YEAR(TODAY()),3,8-1))
Year= Today or the Date field’s year
Month= 3
NthDay = 2
DayOfWeek = Sun = 1
Daylight Savings Time ends on the first Sunday in November. The formula to get this date dynamically is:
DATE( YEAR(TODAY()),11,(1*7+1)) – WEEKDAY(DATE(YEAR(TODAY()),11,8-1))
Year= Today or the Date field’s year
Month= 11
NthDay = 1
DayOfWeek = Sun = 1
Lastly put them together with your Time formula and use them to check for DST:
IF({!ActivityDateTime.ActivityDateTime} >= DATE( YEAR(TODAY()),3,(2*7+1)) – WEEKDAY(DATE(YEAR(TODAY()),3,8-1)) &&
{!ActivityDateTime.ActivityDateTime} <= DATE( YEAR(TODAY()),11,(1*7+1)) – WEEKDAY(DATE(YEAR(TODAY()),11,8-1)) ,
TEXT(IF( OR( VALUE( MID( TEXT( {!ActivityDateTime.ActivityDateTime} – 5/24 ), 12, 2 ) ) = 0, VALUE( MID( TEXT( {!ActivityDateTime.ActivityDateTime} – 5/24 ), 12, 2 ) ) = 12 ), 12, VALUE( MID( TEXT( {!ActivityDateTime.ActivityDateTime} – 5/24 ), 12, 2 ) ) – IF( VALUE( MID( TEXT( {!ActivityDateTime.ActivityDateTime} – 5/24 ), 12, 2 ) ) < 12, 0, 12 ) )) & “:” & MID( TEXT( {!ActivityDateTime.ActivityDateTime} – 5/24 ), 15, 2 ) ,
TEXT(IF( OR( VALUE( MID( TEXT( {!ActivityDateTime.ActivityDateTime} – 6/24 ), 12, 2 ) ) = 0, VALUE( MID( TEXT( {!ActivityDateTime.ActivityDateTime} – 6/24 ), 12, 2 ) ) = 12 ), 12, VALUE( MID( TEXT( {!ActivityDateTime.ActivityDateTime} – 6/24 ), 12, 2 ) ) – IF( VALUE( MID( TEXT( {!ActivityDateTime.ActivityDateTime} – 6/24 ), 12, 2 ) ) < 12, 0, 12 ) )) & “:” & MID( TEXT( {!ActivityDateTime.ActivityDateTime} – 6/24 ), 15, 2 )
)
If the date of the activity is greater or equal to the first day of DST AND less than or equal to the last day of DST…Use the 5 hour Offset formula
If not, use the 6 hour Offset formula
