1

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
  • @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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.