To maintain the correct date and time, including handling Daylight Saving Time (DST), in a formula field in Salesforce, you can use the following approach. Salesforce’s built-in formulas for date and time fields should automatically account for DST changes as long as your organization’s time zone settings are configured correctly.
Here’s how you can create a formula field.
Ensure Correct Time Zone Settings:
Make sure that your Salesforce organization’s time zone settings are configured correctly. You can do this by going to “Setup” > “Company Settings” > “Company Information.” Verify that the “Time Zone” field is set to the correct time zone for your organization. Salesforce should automatically adjust for Daylight Saving Time changes based on this setting.
Use a Formula to Adjust for DST:
In your formula field, you can use the following formula to adjust for Daylight Saving Time (DST):
IF( AND(CreatedDate >= DATETIMEVALUE(“2023-03-11 00:00:00”),CreatedDate <= DATETIMEVALUE(“2023-11-04 00:00:00”)) ,
IF(VALUE(LPAD(MID(TEXT( Your_Date_and_Time_Field__c – 0.20833333333), 12, 5),2)) = 12 ,
MID(TEXT(Your_Date_and_Time_Field__c – 0.20833333333), 12, 5)+’ PM’,
IF(VALUE(LPAD(MID(TEXT(Your_Date_and_Time_Field__c – 0.20833333333), 12, 5),2)) < 12, IF(VALUE(LPAD(MID(TEXT(Your_Date_and_Time_Field__c + – 0.20833333333), 12, 5),2)) = 00, TEXT(12)+MID(TEXT(Your_Date_and_Time_Field__c – 0.20833333333), 14, 3), IF(VALUE(LPAD(MID(TEXT(Your_Date_and_Time_Field__c – 0.20833333333), 12, 5),2)) < 10, MID(TEXT(Your_Date_and_Time_Field__c – 0.20833333333), 13, 4), MID(TEXT(Your_Date_and_Time_Field__c – 0.20833333333), 12, 5)))+’ AM’, TEXT(VALUE(MID(TEXT(Your_Date_and_Time_Field__c – 0.20833333333), 12, 2))-12)+MID(TEXT(Your_Date_and_Time_Field__c – 0.20833333333), 14, 3)+’ PM’))
,
IF(VALUE(LPAD(MID(TEXT( Your_Date_and_Time_Field__c – 0.25), 12, 5),2)) = 12 ,
MID(TEXT(Your_Date_and_Time_Field__c – 0.25), 12, 5)+’ PM’,
IF(VALUE(LPAD(MID(TEXT(Your_Date_and_Time_Field__c – 0.25), 12, 5),2)) < 12, IF(VALUE(LPAD(MID(TEXT(Your_Date_and_Time_Field__c + – 0.25), 12, 5),2)) = 00, TEXT(12)+MID(TEXT(Your_Date_and_Time_Field__c – 0.25), 14, 3), IF(VALUE(LPAD(MID(TEXT(Your_Date_and_Time_Field__c – 0.25), 12, 5),2)) < 10, MID(TEXT(Your_Date_and_Time_Field__c – 0.25), 13, 4), MID(TEXT(Your_Date_and_Time_Field__c – 0.25), 12, 5)))+’ AM’, TEXT(VALUE(MID(TEXT(Your_Date_and_Time_Field__c – 0.25), 12, 2))-12)+MID(TEXT(Your_Date_and_Time_Field__c – 0.25), 14, 3)+’ PM’))
)
This formula subtracts 5 hours (5/24) from Your_Date_and_Time_Field__c (assuming the default time zone is GMT) and adjusts it based on the day of the week. This is a simplified formula and may need further adjustments depending on your specific requirements and time zone.
Save the Formula Field:
Save your custom formula field.
Please note that this formula might need adjustments based on the specific requirements of your organization and time zone rules.
Another approach would be to utilize Apex code (Salesforce’s programming language) within triggers, classes, or processes to handle time and time zone conversions more precisely.
Conclusion:
while you can create formula fields in Salesforce to reflect whether it’s DST or standard time based on predefined dates, these formulas are static and not capable of dynamically adjusting for changing DST rules or time zones. Implementing dynamic DST calculations would likely require more complex logic and potentially external systems or custom code integration within Salesforce
Related Links:
https://merfantz.com/blog/how-to-add-the-organization-business-hours-in-salesforce-processes/
https://merfantz.com/blog/use-of-formula-field-and-map-fields-in-salesforce/
Author Bio
