Add Days to a Salesforce Formula DateTime Field and Adjust for Daylight Savings Time

Adding days to a DateTime field can lead to an unexpected time when the added date passes a daylight savings time. For example, if 7 days is added to 3/9/2018 10:00 AM, the new date will 3/16/2018 11:00 AM because it has passed the daylight savings time date. This occurs because the date is stored in Salesforce’s databases as GMT, and adding days to a GMT date doesn’t adjust for users in zones that observe daylight savings time.

The following works only when your users are always in a timezone that observes daylight savings time.

/* daylight savings starts March 11. Daylight savings ends November 4 */
/* adjust time if 7 day appointment will pass over daylight savings date */
Your_DateTime_Field__c + 7 +
IF(3 = MONTH(DATEVALUE(Your_DateTime_Field__c+7)),
 IF( AND(DAY(DATEVALUE(Your_DateTime_Field__c+7))>=11, DAY(DATEVALUE(Your_DateTime_Field__c+7))<=18),
  -(60/1440),
  0
 )
 ,
0
)
+ 
IF(11 = MONTH(DATEVALUE(Your_DateTime_Field__c+7)),
 IF(AND(DAY(DATEVALUE(Your_DateTime_Field__c+7))>=4, DAY(DATEVALUE(Your_DateTime_Field__c+7))<=11),
  (60/1440),
  0
 )
,
0
)

Related