Can Daylight Savings Time be Automated in a Formula Field

formula-fieldtimezone

I support a vaccine management system in salesforce. After a vaccine has been administered for a contact, a future appointment is scheduled immediately for the 2nd dose.

There is a formula field on the Service Appointment object that offsets an hour to account for daylight savings time for 2nd doses.

Currently, we are manually updating the User time zone to accommodate for DST.

  1. Is there a way to automate this so we don't have to adjust the time zone manually?

Also, after the time zone has been adjusted, all existing appointments do not change and have to be manually updated to reflect the new time.

  1. Is there a way to automate this as well?

Here is the formula field on the Service Appointment object

IF(VALUE(LPAD(MID(TEXT( SchedStartTime -(5.00/24)), 12, 5),2)) = 12 ,
MID(TEXT( SchedStartTime -(5.00/24)), 12, 5)+' PM',

IF(VALUE(LPAD(MID(TEXT( SchedStartTime -(5.00/24)), 12, 5),2)) < 12, IF(VALUE(LPAD(MID(TEXT( SchedStartTime -(5.00/24)), 12, 5),2)) = 00, TEXT(12)+MID(TEXT( SchedStartTime -(5.00/24)), 14, 3),

IF(VALUE(LPAD(MID(TEXT( SchedStartTime -(5.00/24)), 12, 5),2)) < 10,
MID(TEXT( SchedStartTime -(5.00/24)), 13, 4), MID(TEXT( SchedStartTime -(5.00/24)), 12, 5)))+' AM', TEXT(VALUE(MID(TEXT( SchedStartTime -(5.00/24)), 12, 2))-12)+MID(TEXT( SchedStartTime -(5.00/24)), 14, 3)+' PM'))

Best Answer

As per the documentation:

There’s no way to determine a user’s time zone in a formula. If all of your users are in the same time zone, you can adjust the time zone difference by adding or subtracting the time difference between the users’ time zone and GMT to your converted values. However, since time zones can be affected by Daylight Saving Time, and the start and end dates for DST are different each year, this is difficult to manage in a formula. We recommend using Apex for transactions that require converting between Date/Time values and Text or Date values

(Emphasis is mine)

I therefore believe you need to remove the formula field and replace it by a regular Datetime field that is calculated in Apex (when creating the service appointment).

Note that it is important to calculate this field's value in the right way, otherwise you will simply hit the same sorts of problem again.

Basically you need to split the Datetime for the first injection into separate Date and Time values (since these then don't actually have a time zone), update the Date then recombine the values again into a Datetime. An example:

Datetime firstInjection = Datetime.newInstance(2021, 8, 16, 16, 30, 00); // actually take from the first injection date field
Integer offsetInDays = 90; // Or however many days it should be
Datetime secondInjection = Datetime.newInstance(firstInjection.date().addDays(offsetInDays), firstInjection.time());

This ensures that the secondInjection date/time is calculated to use the same time-of-day as the firstInjection date/time and at a date offset from the firstInjection date, irrespective of DST transitions.

If you need to target a time zone, in this calculation, that is not the same as the contextual user's one then you will most likely need to jump a lot more hoops to get this correctly functioning, applying offsets between the contextual user's time zone and the target time zone on the appropriate dates. Take a look at the TimeZone documentation for some more information and an example of using TimeZone.getOffset.

Related Topic