0

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'))
5
  • I am a bit confused. All Datetime values in the database are stored in UTC (though called GMT by Salesforce) and it is simply when rendering for presentation that user time zones are applied. Is it that you want to end up with the same time-of-day even when traversing a DST transition date?
    – Phil W
    Nov 3, 2021 at 7:54
  • On the assumption this is what you want to do, please take a look at the documentation which says you should use Apex to handle this (scroll right to the end) because formulae cannot access user time zone.
    – Phil W
    Nov 3, 2021 at 8:01
  • We are trying to avoid using Apex if possible. What's happening is when the formula kicks in, all 2nd dose appointment times are being changed by an hour and we're having to go thru the system to reschedule them back to the original time. Nov 3, 2021 at 15:46
  • As per the documentation, I suggest you basically have little choice other than to use Apex and replace the formula with a (trigger calculated) field.
    – Phil W
    Nov 3, 2021 at 15:50
  • Thanks Phil. Yeah I figured we would have to use a trigger but I'm being asked to try and make the formula field dynamic and I'm not seeing how that can be done. I suggested if I could change the company default time zone to a standard time, but they want to keep it at Central Daylight Time with a formula in place. Anyway, thanks for confirming that it would need to be done in Apex. Nov 3, 2021 at 16:25

1 Answer 1

2

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .