Daylight Saving Time or not?

Even though in Salesforce you can set the correct time zone, in some cases you might be unhappily surprised by a time that is suddenly displayed in GMT rather than your own time zone.

When and why does this occur?

This problem occurs when a date, time or Date/Time value is used in a formula of which the output type is not date or Date/Time.

By default all Date/Time values are actually saved in GMT / UTC. The value is then only converted to the org's or user's time zone by the user interface.

By use of the Salesforce Inspector browser plugin you can easily see this in action. For clarity, from my standpoint, working with only customers in the Central European Time zone (Amsterdam, Brussels, Berlin, Paris etc.) this blog assumes the output should be in CET (GMT+1 in winter, GMT+2 in summer). Find a random Date/Time field on a record and take not of the time you see. Then check out the record using Salesforce Inspector, find the field, you have just been looking at. When in your user interface it looks like 29-03-2023 12:00, turns out to actually have 2023-03-29T10:00:00.000+0000 as the API value.

When you use this Date/Time value in a formula and the output does not contain a date, it is never possible to determine whether it is daylight saving time or standard time, and the correct adjustment cannot be calculated. Furthermore, on the last Sundays of March and October, it is also impossible to determine whether it is daylight saving time or standard time without the time information.

So, when you use the Date/Time value to convert it into a Text or Time value, you will see the time in GMT and not the time in your local timezone (if it differs from GMT).

When you want to include the date and time of an appointment in a user-friendly format in an email, you will probably make use of one or more formulas that have text or time as their output.

Obviously, you do not want to communicate an incorrect appointment time to the customer. To remedy this you can build in a correction in your formula field, but you also have to take into account whether it's currently daylight saving time or standard time. And that's when the formula can quickly become complex.

Here is the formula you need, where you should replace "myDateTimeField" with the field you want to convert to the correct time indication. Again: this applies to CET / CEST (Amsterdam, Brussels, Berlin, Paris etc.)

myDateTimeField
+
IF(
    OR(
        /* april - september : always summertime */
        MONTH( DATEVALUE( myDateTimeField ) ) < 10 &&
        MONTH( DATEVALUE( myDateTimeField ) ) > 3,
        
        /* after last sunday of march : always summertime */
        MONTH( DATEVALUE( myDateTimeField ) ) = 3 &&
        DATEVALUE(myDateTimeField) >
        DATE(
            YEAR( DATEVALUE( myDateTimeField ) ),
            3,
            24
        ) +
        8 -
        WEEKDAY(
            DATE(
                YEAR( DATEVALUE( myDateTimeField ) ),
                3,
                24
            )
        ),
        
        /* before last sunday of october : always summertime */
        MONTH( DATEVALUE( myDateTimeField ) ) = 10 &&
        DATEVALUE(myDateTimeField) <
        DATE(
            YEAR( DATEVALUE( myDateTimeField ) ),
            10,
            24
        ) +
        8 -
        WEEKDAY(
            DATE(
                YEAR( DATEVALUE( myDateTimeField ) ),
                10,
                24
            )
        ),

        /* on last sunday of march after 3 AM CEST = 1 AM UTC: always summertime */
        MONTH( DATEVALUE( myDateTimeField ) ) = 3 &&
        DATEVALUE(myDateTimeField) = 
        DATE(
            YEAR( DATEVALUE( myDateTimeField ) ),
            3,
            24
        ) +
        8 -
        WEEKDAY(
            DATE(
                YEAR( DATEVALUE( myDateTimeField ) ),
                3,
                24
            )
        ) &&
        HOUR(
            TIMEVALUE ( myDateTimeField )
        ) >= 1,
        
        /* on last sunday of october before 2 AM CET = 1 AM UTC: always summertime */
        MONTH( DATEVALUE( myDateTimeField ) ) = 10 &&
        DATEVALUE(myDateTimeField) = 
        DATE(
            YEAR( DATEVALUE( myDateTimeField ) ),
            10,
            24
        ) +
        8 -
        WEEKDAY(
            DATE(
                YEAR( DATEVALUE( myDateTimeField ) ),
                10,
                24
            )
        ) &&
        HOUR(
            TIMEVALUE ( myDateTimeField  )
        ) < 1
    ),
    2,
    1
)
/ 24

This formula can be broken down as follows:

The outermost part adds 2 hours to the API value when it is Daylight Saving Time, and 1 hour when it is not. The /24 at the end makes sure you add hours instead of days.

myDateTimeField
+
IF(
    /* isSummerTime * /,
    2,
    1
)
/ 24

Within the tested condition of this IF formula, the actual formula that can tell whether is or is not daylight saving time is embedded. The outcome of that formula is a Boolean: true or false. For this we tackle the time periods from long to short:

OR(
    /* april - september : always summertime */,
    
    /* after last sunday of march : always summertime */,

    /* before last sunday of october : always summertime */,

    /* on last sunday of march after 3 AM CEST = 1 AM UTC: always summertime */,
    
    /* on last sunday of october before 2 AM CET = 1 AM UTC: always summertime */
)
  1. Between April and September all days of the month are in daylight saving time
  2. In March each day after the month's last Sunday is daylight saving time
  3. In October every day before the last Sunday of the month is daylight saving time
  4. On the last Sunday of March daylight saving time starts at 1:00 GMT
  5. On the last Sunday of October daylight saving time ends at 0:00 GMT
  6. On the last Sunday or October the hour between 2:00 and 3:00 occurs twice, first in daylight saving time and the again in 'winter' time. Therefore for this particular hour you cannot tell if that moment was befor or after daylight saving time ended. Best practice would be to not plan any important events during those hours of the night.

The 5 separate tests for daylight saving time are summarized in the OR formula. So, if any of those individual tests is true, the result of the OR formula is true, indicating that it is daylight saving time.

The individual tests:

Between April and September all days of the month are in daylight saving time

MONTH( DATEVALUE( myDateTimeField ) ) < 10 &&
MONTH( DATEVALUE( myDateTimeField ) ) > 3,

In March each day after the month's last Sunday is daylight saving time

MONTH( DATEVALUE( myDateTimeField ) ) = 3 &&
DATEVALUE(myDateTimeField) >
DATE(
    YEAR( DATEVALUE( myDateTimeField ) ),
    3,
    24
) +
8 -
WEEKDAY(
    DATE(
        YEAR( DATEVALUE( myDateTimeField ) ),
        10,
        24
    )
)

In October every day before the last Sunday of the month is daylight saving time

MONTH( DATEVALUE( myDateTimeField ) ) = 10 &&
DATEVALUE(myDateTimeField) <
DATE(
    YEAR( DATEVALUE( myDateTimeField ) ),
    10,
    24
) +
8 -
WEEKDAY(
    DATE(
        YEAR( DATEVALUE( myDateTimeField ) ),
        10,
        24
    )
)

On the last Sunday of March daylight saving time starts at 1:00 GMT

MONTH( DATEVALUE( myDateTimeField ) ) = 3 &&
DATEVALUE(myDateTimeField) = 
DATE(
    YEAR( DATEVALUE( myDateTimeField ) ),
    3,
    24
) +
8 -
WEEKDAY(
    DATE(
        YEAR( DATEVALUE( myDateTimeField ) ),
        3,
        24
    )
) &&
HOUR(
    TIMEVALUE ( myDateTimeField )
) >= 1

On the last Sunday of October daylight saving time ends at 0:00 GMT

MONTH( DATEVALUE( myDateTimeField ) ) = 10 &&
DATEVALUE(myDateTimeField) = 
DATE(
    YEAR( DATEVALUE( myDateTimeField ) ),
    10,
    24
) +
8 -
WEEKDAY(
    DATE(
        YEAR( DATEVALUE( myDateTimeField ) ),
        10,
        24
    )
) &&
HOUR(
    TIMEVALUE ( myDateTimeField  )
) < 1

Pay close attention to the deviation your displayed time has compared to the intended time. Sometimes you need to add the deviation, and other times you need to subtract it. Be cautious not to correct twice or apply two contradictory adjustments that cancel each other out.

If you copy-paste the entire formula into notepad, you can use find and replace to replace all occurences of myDateTimeField to your Date/Time field's API name. Use the first codeblock of this article. This is the entire formula.

Tags: