Becci No Comments

There are times when you want to format a date with the full names of the month and day.  For example:  3/25/20 as Wednesday, March 25, 2020.  This can be useful in templates for reminders to give it less of a ‘form letter’ feel.

The formulas below can be used in several ways.  They can be used to update a field in a Process Builder, used in a flow, or most places a formula can be used to manipulate data.

In the example below I have a flow that runs every day that looks for events coming up in the following week.  Those event records have a custom field that updates with the Date String so it can be used in a reminder template.

To format the date we first use a case statement to find the day of the week:

CASE( MOD( {!ActivityDateTime.ActivityDate} – DATE(1900, 1, 7), 7), 0, “Sunday”, 1, “Monday”, 2, “Tuesday”, 3,

“Wednesday”, 4, “Thursday”, 5, “Friday”, 6, “Saturday”, “Error”)

Next we use a case statement to get the Month name:

CASE(MONTH({!ActivityDateTime.ActivityDate}),

1, “January”,

2, “February”,

3, “March”,

4, “April”,

5, “May”,

6, “June”,

7, “July”,

8, “August”,

9, “September”,

10, “October”,

11, “November”,

12, “December”,

“None”

)

Finally we can use the DAY and YEAR function in Salesforce and convert those to text:

TEXT(DAY( {!ActivityDateTime.ActivityDate})) & “, ” & TEXT(YEAR({!ActivityDateTime.ActivityDate}))

Once you have the date formatted as you like, you can add text to complete the string for your needs:

“On ” & CASE( MOD( {!ActivityDateTime.ActivityDate} – DATE(1900, 1, 7), 7), 0, “Sunday”, 1, “Monday”, 2, “Tuesday”, 3, “Wednesday”, 4, “Thursday”, 5, “Friday”, 6, “Saturday”, “Error”) & “, ” &

CASE(MONTH({!ActivityDateTime.ActivityDate}),

1, “January”,

2, “February”,

3, “March”,

4, “April”,

5, “May”,

6, “June”,

7, “July”,

8, “August”,

9, “September”,

10, “October”,

11, “November”,

12, “December”,

“None”

)& ” ” &

TEXT(DAY( {!ActivityDateTime.ActivityDate})) & “, ” & TEXT(YEAR({!ActivityDateTime.ActivityDate}))

The above formula with display as:  On Wednesday, March 25, 2020

 See the Format Salesforce DateTime as Strings with formatted Time Tip and Trick for more formula examples.

Leave a Reply

Your email address will not be published. Required fields are marked *