How to output [MMMM dd yyyy hh:mm AM/PM] using Text formula on a DateTime field?

Question

Good day StackExchange!

High level, my goal is to take a date/time value and use a formula to display it as a customer friendly long date (MMMM-DD,YYYY at HH:MM AM/PM) string output for email templates.

My issue is each formula output only gives me half of what I'd like.

A Date/Time formula output gives me "1/29/2020 1:00 PM"

A Text formula output using CASE,DAY,YEAR and TIMEVALUE gives me "January 29 2020 13:00:00.000"

Is it possible to get an output of "January 29 2020 1:00 PM" using only one formula field, and without creating additional fields as value holders?

Apex is a last option as that has a budget cost.

Any help graciously appreciated. My current formula below:

CASE(
  (MONTH(DATEVALUE(Next_Meeting__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(Next_Meeting__c)))) & " " & 
TEXT((YEAR(DATEVALUE(Next_Meeting__c)))) & " " & 
TEXT(TIMEVALUE(Next_Meeting__c - (( $User.UserTimezoneOffset__c + Lead1__r.TZ_Offset__c) * 60)/1440))

Answer 1

The below seemed to work well except with regards to Daylight Savings Time. I'm hopeful, depending on how your UserTimezoneOffset field is set, that this might not be an issue.

For reference, this thread contains where I got the initial structure of the formula from and good discussion about doing what you're looking for. You'll be parsing through the timevalue text with some logic for the various scenarios

• exactly noon or midnight
• greater than 12 hour value
• single digit hour

That plus the final logic for AM vs. PM (greater than 12 hour value again).

Your formula in your question would be used for everything except the time part at the end. I was testing on LastModifiedDate and using a hard coded offset (4/24) for EDT. I'd have to change it to 5/24 for EST

IF(OR(
    VALUE(MID(TEXT(LastModifiedDate - 4/24), 12, 2)) = 0,
    VALUE(MID(TEXT(LastModifiedDate - 4/24), 12, 2)) = 12
    ),
    "12", 
    TEXT(VALUE(MID(TEXT(LastModifiedDate - 4/24), 12, 2)) - IF(VALUE(MID( TEXT(LastModifiedDate - 4/24), 12, 2 )) < 12, 0, 12)) 
) & 
":" & 
MID(TEXT(LastModifiedDate), 15, 2) & 
":" & 
MID(TEXT(LastModifiedDate), 18, 2) & 
" " & 
IF(VALUE(MID(TEXT(LastModifiedDate - 4/24), 12, 2 )) < 12, "AM", "PM")