2

I think a formula is possible for this but I am new to SF so stitching it all together is challenging.

I am trying to convert a date/time field to a readable text field so that I can use it in email templates.

For example, 2021-06-25, 4:00 p.m. becomes the much more client-friendly Friday, June 25 at 4:00 PM.

I've successfully done this with the following formula:

CASE( 
    MOD( DATEVALUE( Trial_Lesson_Date_Time__c ) - DATE(1900, 1, 7), 7), 
    0, "Sunday", 
    1, "Monday", 
    2, "Tuesday", 
    3, "Wednesday", 
    4, "Thursday", 
    5, "Friday", 6, 
    "Saturday","Trial Lesson Date/Time Empty"
) 
 + ", " +
CASE(
    MONTH(
        DATEVALUE(Trial_Lesson_Date_Time__c)),
        1, "January",
        2, "February",
        3, "March",
        4, "April",
        5, "May",
        6, "June",
        7, "July",
        8, "August",
        9, "September",
        10, "October",
        11, "November",
        "December"
) 
 + " " + 
TEXT(DAY(DATEVALUE(Trial_Lesson_Date_Time__c)))
 + " at " + 
(
    IF(
        VALUE(LPAD(MID(TEXT(Trial_Lesson_Date_Time__c - 0.16666667), 12, 5),2)) = 12 ,
        MID(TEXT(Trial_Lesson_Date_Time__c - 0.16666667), 12, 5) + 'PM',
        IF(VALUE(LPAD(MID(TEXT(Trial_Lesson_Date_Time__c - 0.16666667), 12, 5),2)) < 12, 
            IF(VALUE(LPAD(MID(TEXT(Trial_Lesson_Date_Time__c + 0.16666667), 12, 5),2)) = 00, 
                TEXT(12)+MID(TEXT(Trial_Lesson_Date_Time__c + 0.16666667), 14, 3), +
                 IF(VALUE(LPAD(MID(TEXT(Trial_Lesson_Date_Time__c + 0.16666667), 12, 5),2)) < 10,
                     + MID(TEXT(Trial_Lesson_Date_Time__c + 0.16666667), 13, 4),
                     + MID(TEXT(Trial_Lesson_Date_Time__c + 0.16666667), 12, 5)
            )
        ) +' AM', 
        TEXT(VALUE(MID(TEXT(Trial_Lesson_Date_Time__c + 0.16666667), 12, 2)) - 12)
        + MID(TEXT(Trial_Lesson_Date_Time__c + 0.16666667), 14, 3) +' PM'
        )
    )
)

The only problem is that this doesn't work after daylight savings ends/starts etc.

I know that it is probably possible to add conditions to this formula: If the date/time field is after Nov 6, 2021, it should read - 0.2083333 (or -5/24 instead of -4/24). After March 13, 2022, it should change back to -0.16666667 again.

Any guidance on how to write this all into one formula?

Many thanks!

2
  • I've made an attempt to format the formula so that it's readable. Apologies if I've introduced a mistake in doing so.
    – Derek F
    Aug 16, 2021 at 1:57
  • thank you @DerekF! Aug 16, 2021 at 13:27

1 Answer 1

3

Don't do this in a formula. You're setting yourself up for failure. For example, you have no idea if the government will abolish DST in 2022, or if they'll move it to a different day, or even if someone decides it should be offset :45 minutes instead of an hour, etc.

The best way to handle this is in Apex. You'll already have the GMT time in, say, a trigger, and then you can use the DateTime.format() method to provide the correct time zone from a GMT instance, and it will be formatted correctly. The DateTime and TimeZone classes use the Unix-like behavior of automatically adjusting for time zone, even leap seconds, if necessary.

3
  • Thank you for this insight, @sfdcfox. At this point, I don't know how to handle this in Apex. Can you direct me to something that might explain how I could create this properly? Aug 16, 2021 at 13:29
  • 1
    @RebeccaLane You can start with the trailhead on triggers in general, and the actual code you need would be something like: record.ClientFriendlyDate__c = record.Trial_Lesson_Date_Time__c.format('EEEE, MMMM dd \'at\' HH:mm aa', record.ClientTimeZone__c); (the TZ is optional, but will default to the user's time zone if not specified). Note that this will be a normal, editable text field, that will be set by the trigger.
    – sfdcfox
    Aug 16, 2021 at 15:26
  • @RebeccaLane See SimpleDateFormat for information on how to format the string as you desire, and DateTime for more information on the DateTime class.
    – sfdcfox
    Aug 16, 2021 at 15:28

You must log in to answer this question.

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