3

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))
  • 2
    For future reference, please prefix each line with four spaces instead of using the backtick (`) character. That is only intended for inline code references, not entire blocks of code. – sfdcfox Jan 29 at 18:56
  • Will do, thanks fox! – Sovereignty Jan 29 at 19:05
1
+100

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")
| improve this answer | |
  • Hi Kris, reviewing and will award bounty appropriately. Thanks for replying! – Sovereignty Feb 4 at 18:08

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.