Ask Search:
Wendy KwanWendy Kwan 

datevalue

How do I make this custom field date to display April 1, 2018 instead of 4/1/2018?
I would like to figure out how to write the function in the Custom Field Definition Detail next to SBQQ__Account__r.op2_Original_Contract_Date__c

Any help will be much appreciated.  Thanks!
User-added image
 
Best Answer chosen by Wendy Kwan
Tom HoffmanTom Hoffman
Not sure if CPQ will let you modify that field if its part of the managed package; if not you could create your own field with a formula like this:

Case(Month(YourDateField),
1,'January',
2,'Febuary',
3,'March',
4,'April',
5,'May',
6,'June',
7,'July',
8,'August',
9,'September',
10,'October',
11,'November',
'December')&' '&Day(YourDateField)&', '&Year(YourDateField)

This will give you the Month DD, YYYY format. 

All Answers

Tom HoffmanTom Hoffman
Not sure if CPQ will let you modify that field if its part of the managed package; if not you could create your own field with a formula like this:

Case(Month(YourDateField),
1,'January',
2,'Febuary',
3,'March',
4,'April',
5,'May',
6,'June',
7,'July',
8,'August',
9,'September',
10,'October',
11,'November',
'December')&' '&Day(YourDateField)&', '&Year(YourDateField)

This will give you the Month DD, YYYY format. 
This was selected as the best answer
Sunil SarillaSunil Sarilla
+Tom
I would modify the formula slightly as below
Case(Month(YourDateField),
1,'January',
2,'Febuary',
3,'March',
4,'April',
5,'May',
6,'June',
7,'July',
8,'August',
9,'September',
10,'October',
11,'November',
'December'
)&' '&TEXT(DAY(YourDateField))&', '&TEXT(YEAR(YourDateField))
formula will not accept mix data types, so you will need to convert the DAY and YEAR into a text values
 
Tom HoffmanTom Hoffman
Thanks for cleaning it up!
Wendy KwanWendy Kwan
 I got this Error: Incorrect number of parameters for function 'Case()'. Expected 24, received 25

Case(Month(SBQQ__Account__r.op2_Original_Contract_Date__c),
1,'January',
2,'Febuary',
3,'March',
4,'April',
5,'May',
6,'June',
7,'July',
8,'August',
9,'September',
10,'October',
11,'November',
12,'December')&' '&TEXT(Day(SBQQ__Account__r.op2_Original_Contract_Date__c))&', '&TEXT(Year(SBQQ__Account__r.op2_Original_Contract_Date__c))


What did I do wrong?
 
Tom HoffmanTom Hoffman
Case functions need an 'everything else equals this' statement at the end.  So if we define months 1 thru 11, we don't need to define 12, that is our 'everything else'.  This should work for you:

Case(Month(SBQQ__Account__r.op2_Original_Contract_Date__c),
1,'January',
2,'Febuary',
3,'March',
4,'April',
5,'May',
6,'June',
7,'July',
8,'August',
9,'September',
10,'October',
11,'November',
'December')
&' '&TEXT(Day(SBQQ__Account__r.op2_Original_Contract_Date__c))&', ' &TEXT(Year(SBQQ__Account__r.op2_Original_Contract_Date__c))

Here is better breakdown of the Case() function-

A case function has 4 components:
Case(Expression, result1, return1, result2, return2, catch-all)
1: Expression: the condition you are evaluating
2: Result: the value you are checking for
3: Return: what you want to return for each value
4: Catch-all: if none of these defined results are true, then do this
 
Wendy KwanWendy Kwan
HI Tom,

I have updated as suggested and the new error is:
Error: Formula result is data type (Text), incompatible with expected data type (Date).

Case(Month(SBQQ__Account__r.op2_Original_Contract_Date__c),
1,'January',
2,'Febuary',
3,'March',
4,'April',
5,'May',
6,'June',
7,'July',
8,'August',
9,'September',
10,'October',
11,'November',
'December')
&' '&TEXT(Day(SBQQ__Account__r.op2_Original_Contract_Date__c))&', ' &TEXT(Year(SBQQ__Account__r.op2_Original_Contract_Date__c))

Wendy
Tom HoffmanTom Hoffman
Click the 'Previous' button, change the formula output type from Date to Text, that should clear it up. 
Wendy KwanWendy Kwan
Tom, it is working!  Thank you so much for your help.

Thank you to Sunil also!