Org Time Zone with DST enabled and DATEVALUEs in a Formula

Question

Can anyone tell me what is wrong with this formula, please?

This has been working correctly, but for no apparent reason seems to be display 1 hour earlier now.

I checked the DST setting in company information (although currently we are in GMT and not British Summer Time (BST yet) and everything then appeared to work correctly.

My displayed value is 1 hour earlier than in the originating field.

CASE(
  MONTH( DATEVALUE(zoom_app__Zoom_Webinar__r.zoom_app__Start_Time__c) ),
  1, "January",
  2, "February",
  3, "March",
  4, "April",
  5, "May",
  6, "June",
  7, "July",
  8, "August",
  9, "September",
  10, "October",
  11, "November",
  12,"December",
   "")

    &" "& 
TEXT(DAY(DATEVALUE(zoom_app__Zoom_Webinar__r.zoom_app__Start_Time__c)))&

CASE(
     DAY(DATEVALUE(zoom_app__Zoom_Webinar__r.zoom_app__Start_Time__c)),
     1,"st",
     2,"nd",
     3,"rd",
     4,"th",
     5,"th",
     6,"th",
     7,"th",
     8,"th",
     9,"th",
     10,"th",
     11,"th",
     12,"th",
     13,"th",
     14,"th",
     15,"th",
     16,"th",
     17,"th",
     18,"th",
     19,"th",
     20,"th",
     21,"st",
     22,"nd",
     23,"rd",
     24,"th",
     25,"th",
     26,"th",
     27,"th",
     28,"th",
     29,"th",
     30,"th",
     31,"th",
    "")

     &" "&
IF(
        OR(
        VALUE( MID( TEXT( zoom_app__Zoom_Webinar__r.zoom_app__Start_Time__c - 0 ), 12, 2 ) ) = 0,
        VALUE( MID( TEXT( zoom_app__Zoom_Webinar__r.zoom_app__Start_Time__c - 0 ), 12, 2 ) ) = 12
        ),
  "12",
  TEXT( VALUE( MID( TEXT( zoom_app__Zoom_Webinar__r.zoom_app__Start_Time__c - 0 ), 12, 2 ) ) 
   -
   IF( 
     VALUE( MID( TEXT( zoom_app__Zoom_Webinar__r.zoom_app__Start_Time__c - 0 ), 12, 2 ) ) < 12, 
     0, 
     12 
   ) 
  )
)
& ":" &
MID( TEXT( zoom_app__Zoom_Webinar__r.zoom_app__Start_Time__c - 0 ), 15, 2 )
& " " &

IF(
  VALUE( MID( TEXT( zoom_app__Zoom_Webinar__r.zoom_app__Start_Time__c - 0 ), 12, 2 ) ) < 12,
  "AM",
  "PM"
)
 &" - "&
IF(
        OR(
        VALUE( MID( TEXT( zoom_app__Zoom_Webinar__r.zoom_app__End_Time__c - 0 ), 12, 2 ) ) = 0,
        VALUE( MID( TEXT( zoom_app__Zoom_Webinar__r.zoom_app__End_Time__c - 0 ), 12, 2 ) ) = 12
        ),
  "12",
  TEXT( VALUE( MID( TEXT( zoom_app__Zoom_Webinar__r.zoom_app__End_Time__c - 0 ), 12, 2 ) ) 
   -
   IF( 
     VALUE( MID( TEXT( zoom_app__Zoom_Webinar__r.zoom_app__End_Time__c - 0 ), 12, 2 ) ) < 12, 
     0, 
     12 
   ) 
  )
)
& ":" &
MID( TEXT( zoom_app__Zoom_Webinar__r.zoom_app__End_Time__c - 0 ), 15, 2 )
& " " &

IF(
  VALUE( MID( TEXT( zoom_app__Zoom_Webinar__r.zoom_app__End_Time__c - 0 ), 12, 2 ) ) < 12,
  "AM",
  "PM"
)

Answer 1

Original Field Value: 31/03/2021, 10:00. Formula Field Value: March 31th 2021 10:00 AM - 12:15 AM (The formula also includes an end time)

The TIMEVALUE is used to be able to adjust the formula timezone misalignment of DATETIME values. (This Salesforce issue highlights the problem - https://success.salesforce.com/ideaView?id=0873A000000E91qQAC (please upvote)).

Another point to note, is that you can get a syntax error of 'Extra IF' (https://trailblazers.salesforce.com/answers?id=9063A000000eAPmQAM), you need to add the + to merge the IF into the rest of the formula.

CASE( MONTH( DATEVALUE(zoom_app__Zoom_Webinar__r.zoom_app__Start_Time__c) ), 1, "January", 2, "February", 3, "March", 4, "April", 5, "May", 6, "June", 7, "July", 8, "August", 9, "September", 10, "October", 11, "November", 12,"December", "")

&" "&

TEXT(DAY(DATEVALUE(zoom_app__Zoom_Webinar__r.zoom_app__Start_Time__c)))&

CASE( DAY(DATEVALUE(zoom_app__Zoom_Webinar__r.zoom_app__Start_Time__c)), 1,"st", 2,"nd", 3,"rd", 4,"th", 5,"th", 6,"th", 7,"th", 8,"th", 9,"th", 10,"th", 11,"th", 12,"th", 13,"th", 14,"th", 15,"th", 16,"th", 17,"th", 18,"th", 19,"th", 20,"th", 21,"st", 22,"nd", 23,"rd", 24,"th", 25,"th", 26,"th", 27,"th", 28,"th", 29,"th", 30,"th", 31,"th", "")

&" "& TEXT(YEAR(Datevalue(zoom_app__Zoom_Webinar__r.zoom_app__Start_Time__c))) &" "& TEXT( IF( OR( VALUE( MID(TEXT(TIMEVALUE( zoom_app__Zoom_Webinar__r.zoom_app__Start_Time__c +1/24 )), 0, 2 ) ) = 0, VALUE( MID(TEXT(TIMEVALUE( zoom_app__Zoom_Webinar__r.zoom_app__Start_Time__c +1/24 )), 0, 2 ) ) = 12 ), 12, VALUE( MID( TEXT( TIMEVALUE( zoom_app__Zoom_Webinar__r.zoom_app__Start_Time__c +1/24) ),0, 2 ) )

- IF( VALUE( MID( TEXT( TIMEVALUE( zoom_app__Zoom_Webinar__r.zoom_app__Start_Time__c ) ), 0, 2 ) ) < 12, 0, 12 ) ) )

& ":"& MID( TEXT(TIMEVALUE( zoom_app__Zoom_Webinar__r.zoom_app__Start_Time__c +1/24 )), 4, 2 ) & " "&

IF( VALUE( MID( TEXT(TIMEVALUE( zoom_app__Zoom_Webinar__r.zoom_app__Start_Time__c )), 0, 2 ) ) < 12, "AM", "PM" ) &" - "& TEXT( IF( OR( VALUE( MID(TEXT(TIMEVALUE( zoom_app__Zoom_Webinar__r.zoom_app__End_Time__c +1/24)), 0, 2 ) ) = 0, VALUE( MID(TEXT(TIMEVALUE( zoom_app__Zoom_Webinar__r.zoom_app__End_Time__c +1/24 )), 0, 2 ) ) = 12 ), 12, VALUE( MID( TEXT( TIMEVALUE( zoom_app__Zoom_Webinar__r.zoom_app__End_Time__c +1/24) ),0, 2 ) )

- IF( VALUE( MID( TEXT( TIMEVALUE( zoom_app__Zoom_Webinar__r.zoom_app__End_Time__c ) ), 0, 2 ) ) < 12, 0, 12 ) ) ) & ":"& MID( TEXT(TIMEVALUE( zoom_app__Zoom_Webinar__r.zoom_app__End_Time__c )), 4, 2 ) & " "&

IF( VALUE( MID( TEXT(TIMEVALUE( zoom_app__Zoom_Webinar__r.zoom_app__End_Time__c )), 0, 2 ) ) < 12, "AM", "PM" )