-1

So I'm trying to create a formula field that will take the termination date in our system and format the month into text but still keep the day and year in a number format. I have the following So for example if the date is 12/1/2023, I want it to return December 1, 2023.

fHCM2__E_End_Date__c is an date field.

This is what I have so far:

CASE(MONTH(DATEVALUE(fHCM2__E_End_Date__c )),
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(DATEVALUE(fHCM2__E_End_Date__c))  & "," 
& TEXT(Year(DATEVALUE(fHCM2__E_End_Date__c )))
2
  • 1
    And what's wrong with the formula that you have? Are you getting an error (edit your question to include the error if you are)? If there isn't a syntax error, you'll need to give us information about the input you're using, the output you're getting, and the output you're expecting.
    – Derek F
    Dec 4, 2023 at 15:36
  • So I'm receiving the following syntax error: Error: Invalid Data. Review all error messages below to correct your data. Syntax error. Missing ')' (Related field: Formula)
    – A Long
    Dec 4, 2023 at 15:42

2 Answers 2

4

When debugging formulas, the first thing you should always do is to format it according to best practice.

tl;dr: Put parts of formulas/functions on separate lines, and indent an extra level when you encounter an open paren.

In many cases, doing this will make it really easy to see issues. In this particular case, the issue is with

& TEXT(Day(DATEVALUE(fHCM2__E_End_Date__c))  & "," 

You've got close parens for DATEVALUE and DAY, but missed the one for TEXT

& TEXT(Day(DATEVALUE(fHCM2__E_End_Date__c)))  & "," 

Knowing how far to go in formula formatting is usually up to experience and personal taste. If we take things closer to the extreme though, the mistake becomes a bit more clear

CASE(
    /* debugging step 1: Parenthesis for MONTH() and DATEVALUE() line up, so this is fine */
    MONTH(
        DATEVALUE(fHCM2__E_End_Date__c )
    ),
    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"
)
/* debugging step 2: This section ends with some indentation, so something is wrong */ 

/* debugging step 6: The only thing left is this TEXT() function, so it must be */
/* the issue */
& TEXT(
    /* debugging step 5: DAY() and DATEVALUE() both have matching parens */
    /* So this is not the issue */
    Day(
        DATEVALUE(fHCM2__E_End_Date__c)
    )  & "," 
    /* debugging step 4: This TEXT() has matching open + close parens */
    /* So this is not the issue */
    & TEXT(
        /* debugging step 3: YEAR() and DATEVALUE() both have matching open + close parens */
        /* So this is not the issue */
        Year(
            DATEVALUE(fHCM2__E_End_Date__c )
        )
    )

5
  • Hello. Thank you. I have added the close parens at the end. I'm now getting the following syntax error when trying to save my formula field: Error: Invalid Data. Review all error messages below to correct your data. Incorrect argument type for function 'DATEVALUE()'. (Related field: Formula)
    – A Long
    Dec 4, 2023 at 15:59
  • @ALong This is why it's important to include as much detail in your questions as you can. What is the field type of fHCM2__E_End_Date__c? Is it a Date or a Datetime, or perhaps a Text? If it's a Date, then there's no need to use DATEVALUE(). If it's a text field, then it needs to match the format specified in the documentation for DATEVALUE(). You should edit your question to add this information.
    – Derek F
    Dec 4, 2023 at 16:04
  • Hello. fHCM2__E_End_Date__c is a date field. So I would not need to use date value anywhere in my formula?
    – A Long
    Dec 4, 2023 at 16:07
  • @ALong correct. DATEVALUE() turns something that is not a date into a date. If your data is already a date, then there's obviously nothing to be converted. The documentation on formula operators and functions is a good thing to reference. It'll tell you what data types each function can take.
    – Derek F
    Dec 4, 2023 at 16:13
  • Thank you. So is this what I would want then: CASE(MONTH(fHCM2__E_End_Date__c )), 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(fHCM2__E_End_Date__c)) & "," & TEXT(Year(fHCM2__E_End_Date__c ))
    – A Long
    Dec 4, 2023 at 16:19
3
& TEXT(Day(DATEVALUE(fHCM2__E_End_Date__c))  & "," 

Is missing a ) near the end. Consider getting a syntax highlighter-enabled text editor, like VS Code, to make it easier to find these kinds of errors. The fix is as follows:

& TEXT(Day(DATEVALUE(fHCM2__E_End_Date__c)))  & "," 

Here's an example of syntax highlighting in VS Code:

Syntax Highlight Showing Missing Paranetheses

As you can see, there's a red parentheses, which indicates a missing closing parentheses. The other colors help you visually pair up the parentheses, which is often helpful as well.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .