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.
