Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Thaier IssaThaier Issa 

How do you account for daylight savings time in a formula?

I have some code here for an important time within my organization that texts clients their appointment times. I noticed an issue that when someone books ahead of daylight savings time, it does not tell them the correct time, it is still an hour behind. I am currently looking for a solution by implementing a formula that accounts for DST but I have not been able to implement a good solution as of yet. 

Here is the formula that displays the correct time for our timezone 

This is called Appointment_Time__c 
IF( OR( VALUE( MID( TEXT( Start_Time__c - (6/24) ), 12, 2 ) ) = 0, VALUE( MID( TEXT( Start_Time__c - (6/24) ), 12, 2 ) ) = 12 ), "12", TEXT( VALUE( MID( TEXT( Start_Time__c - (6/24) ), 12, 2 ) ) - IF( VALUE( MID( TEXT( Start_Time__c - (6/24) ), 12, 2 ) ) < 12, 0, 12 ) ) ) & ":" & MID( TEXT( Start_Time__c - (6/24) ), 15, 2 ) & " " & IF( VALUE( MID( TEXT( Start_Time__c - (6/24) ), 12, 2 ) ) < 12, "AM", "PM" )

And then I have another formula that does the same thing as the first one but it is an hour ahead. 

This is called Appointment_Time_DST__c 
IF( OR( VALUE( MID( TEXT( Start_Time__c - (5/24) ), 12, 2 ) ) = 0, VALUE( MID( TEXT( Start_Time__c - (5/24) ), 12, 2 ) ) = 12 ), "12", TEXT( VALUE( MID( TEXT( Start_Time__c - (5/24) ), 12, 2 ) ) - IF( VALUE( MID( TEXT( Start_Time__c - (5/24) ), 12, 2 ) ) < 12, 0, 12 ) ) ) & ":" & MID( TEXT( Start_Time__c - (5/24) ), 15, 2 ) & " " & IF( VALUE( MID( TEXT( Start_Time__c - (5/24) ), 12, 2 ) ) < 12, "AM", "PM" )
And then finally, I have a formula that determines the date and then it converts the normal time into DST, here is that formula that I have not been able to correctly implement because it does not convert correctly when it should. 
  
IF(OR(AND(DAY(TODAY()) >=10 , MONTH(TODAY()) >= 3), 
AND(DAY(TODAY()) <=3 , MONTH(TODAY()) <=11)) , 
Appointment_Time__c , Appointment_Time_DST__c)
Any help on what I am doing wrong would be greatly appreciated, 

Thanks in advance. 

 
Alain CabonAlain Cabon
Hi,

How do I convert a Date/Time field for any timezone?

This formula will allow you to convert any Date/Time field to display its value in another time zone. It will convert the Date (if necessary), the Time, and will account for Daylight Savings (using US start/end dates for DST).
  
IF ( DATEVALUE(QA_ETOC__c) >=
DATE ( YEAR (DATEVALUE(QA_ETOC__c)),3,1)
+
(
14-
CASE( MOD(DATE ( YEAR (DATEVALUE(QA_ETOC__c)),3,1) - DATE (1900,1,7),7) ,
0,7,MOD(DATE ( YEAR (DATEVALUE(QA_ETOC__c)),3,1) - DATE (1900,1,7),7))
)
&&
DATEVALUE(QA_ETOC__c) <
DATE ( YEAR (DATEVALUE(QA_ETOC__c)),11,1)
+
(
7-
CASE( MOD(DATE ( YEAR (DATEVALUE(QA_ETOC__c)),11,1) - DATE (1900,1,7),7) ,
0,7,MOD(DATE ( YEAR (DATEVALUE(QA_ETOC__c)),11,1) - DATE (1900,1,7),7))
),
LEFT ( TEXT (QA_ETOC__c- 7/24 ), 16),
LEFT ( TEXT (QA_ETOC__c- 8/24), 16)
)


Replace QA_ETOC__c with your Date/Time Field (Tip: copy/paste into Word pad and use Ctrl+H to use the replace functionality)

The above formula works for PST (-8:00 GMT /-7:00GMT during daylight savings).
Change the -7 for Daylight Savings consideration and change the -8 for non-daylight saving consideration. 

If you have IST you can replace both values (-7 and -8) with (+5.5) and it will work accordingly.

  
IF ( DATEVALUE(Start_Time__c) >=
DATE ( YEAR (DATEVALUE(Start_Time__c)),3,1)
+
(
14-
CASE( MOD(DATE ( YEAR (DATEVALUE(Start_Time__c)),3,1) - DATE (1900,1,7),7) ,
0,7,MOD(DATE ( YEAR (DATEVALUE(Start_Time__c)),3,1) - DATE (1900,1,7),7))
)
&&
DATEVALUE(Start_Time__c) <
DATE ( YEAR (DATEVALUE(Start_Time__c)),11,1)
+
(
7-
CASE( MOD(DATE ( YEAR (DATEVALUE(Start_Time__c)),11,1) - DATE (1900,1,7),7) ,
0,7,MOD(DATE ( YEAR (DATEVALUE(Start_Time__c)),11,1) - DATE (1900,1,7),7))
),
LEFT ( TEXT (Start_Time__c- 5/24 ), 16),
LEFT ( TEXT (Start_Time__c- 6/24), 16)
)


 
Alain CabonAlain Cabon
How do I convert a Date/Time field for any timezone?
https://help.salesforce.com/articleView?id=000181642&language=en_US&type=1
Alain CabonAlain Cabon
Template:Daylight saving in time zone/techdoc:

1) DST begins on the second Sunday in March at 2 a.m. local standard time       =>  10 mar 2019

second = 2 weeks = 14 days relatively to 1st of March : DATE ( YEAR (DATEVALUE(Start_Time__c)),3,1) + ( 14 - CASE( MOD(DATE ( YEAR (DATEVALUE(Start_Time__c)),3,1) - DATE (1900,1,7),7) , 0,7,MOD(DATE ( YEAR (DATEVALUE(Start_Time__c)),3,1) - DATE (1900,1,7),7)) )

2) DST ends on the first Sunday in November at 2 a.m. local daylight saving time  =>    3 nov 2019

first = 1 week = 7 days relatively to 1st of November: DATEVALUE(Start_Time__c) < DATE ( YEAR (DATEVALUE(Start_Time__c)),11,1) + ( 7- CASE( MOD(DATE ( YEAR (DATEVALUE(Start_Time__c)),11,1) - DATE (1900,1,7),7) , 0,7,MOD(DATE ( YEAR (DATEVALUE(Start_Time__c)),11,1) - DATE (1900,1,7),7)) ),

7 jan 1900 => Sunday :  MOD ( date1 - DATE (1900,1,7), 7 ) = 0 if date1 is also Sunday ( 1 if date1 is Monday, 2 if date1 is Tuesday, ...)

https://en.wikipedia.org/wiki/Template:Daylight_saving_in_time_zone/techdoc

That is the exact rule used by the generic formula above given by Salesforce and that is why it is always accurate for any years for the USA.

In Europe, we don't have the same rule. DST begins on the last Sunday in March at 1:00 UTC and ends on the last Sunday in October at 1:00 UTC
nazim berchichenazim berchiche
Hi, 

Could you help me with this :
I've got the same issue with Daylight saving Time and I have to adjust it to the Frend Time Zone ! 
When I activate the DST "case" in the Company Settings, it doesn't change anything.

Thanks a lot, 

Nazim
Jon Raney 7Jon Raney 7
what about leap year. seems like that will fail on update