Format Salesforce DateTime as Strings with formatted Time

There are times when you want to format a DateTime field with a standard format for the Time.  For example:  3/25/20 13:30 as 3/25/20 at 1:30 pm.  This can be useful in templates for reminders to give it less of a ‘form letter’ feel.

The formula 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 DateTime String so it can be used in a reminder template.

To format the Time in a datetime field we first find the Hour and convert from military time to standard time:

TEXT(IF( OR( VALUE( MID( TEXT( {!ActivityDateTime.ActivityDateTime} – 5/24 ), 12, 2 ) ) = 0, VALUE( MID( TEXT( {!ActivityDateTime.ActivityDateTime} – 5/24  ), 12, 2 ) ) = 12 ), 12, VALUE( MID( TEXT( {!ActivityDateTime.ActivityDateTime} – 5/24 ), 12, 2 ) ) – IF( VALUE( MID( TEXT( {!ActivityDateTime.ActivityDateTime} – 5/24 ), 12, 2 ) ) < 12, 0, 12 ) ))

NOTE:  In the formula(s) the value 5/24 is the time zone offset value.  5 represent Central Standard Time (GMT – 5).  Update this value accordingly.

Please see the Accounting for Daylight Savings Time in Salesforce Tip and Trick for more information on maintaining these.

Next we find the value for the Minutes:

MID( TEXT(  {!ActivityDateTime.ActivityDateTime} – 5/24 ), 15, 2 )

Finally we evaluate if the time is AM or PM:

IF( VALUE( MID( TEXT(  {!ActivityDateTime.ActivityDateTime} – 5/24), 12, 2 ) ) < 12, “am”, “pm” )

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

TEXT(DATE({!ActivityDateTime.ActivityDateTime} )) & ” at ” & TEXT(IF( OR( VALUE( MID( TEXT( {!ActivityDateTime.ActivityDateTime} – 5/24 ), 12, 2 ) ) = 0, VALUE( MID( TEXT( {!ActivityDateTime.ActivityDateTime} – 5/24  ), 12, 2 ) ) = 12 ), 12, VALUE( MID( TEXT( {!ActivityDateTime.ActivityDateTime} – 5/24 ), 12, 2 ) ) – IF( VALUE( MID( TEXT( {!ActivityDateTime.ActivityDateTime} – 5/24 ), 12, 2 ) ) < 12, 0, 12 ) )) & “:” & MID( TEXT(  {!ActivityDateTime.ActivityDateTime} – 5/24 ), 15, 2 ) & ” ” & IF( VALUE( MID( TEXT(  {!ActivityDateTime.ActivityDateTime} – 5/24), 12, 2 ) ) < 12, “am”, “pm” )

The above formula with display as:  3/25/20 at 1:30 pm

By adding the formula from the Format Salesforce Dates as Strings with Month/Day Names Tip and Trick you can fully format the DateTime field into a string:

“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}))

& ” at ” & TEXT(IF( OR( VALUE( MID( TEXT( {!ActivityDateTime.ActivityDateTime} – 5/24 ), 12, 2 ) ) = 0, VALUE( MID( TEXT( {!ActivityDateTime.ActivityDateTime} – 5/24  ), 12, 2 ) ) = 12 ), 12, VALUE( MID( TEXT( {!ActivityDateTime.ActivityDateTime} – 5/24 ), 12, 2 ) ) – IF( VALUE( MID( TEXT( {!ActivityDateTime.ActivityDateTime} – 5/24 ), 12, 2 ) ) < 12, 0, 12 ) )) & “:” & MID( TEXT(  {!ActivityDateTime.ActivityDateTime} – 5/24 ), 15, 2 ) & ” ” & IF( VALUE( MID( TEXT(  {!ActivityDateTime.ActivityDateTime} – 5/24), 12, 2 ) ) < 12, “am”, “pm” )

The above formula with display as:  On Wednesday, March 25, 2020 at 1:30 pm