+ Start a Discussion
Cullen Combs 1Cullen Combs 1 

I'd like to populate a Date formula field based off of a Text value in a Picklist.

I'd like to leverage the selected value from a Picklist containing the Text version of the twelve months.
Something like:
IF(
ISPICKVAL(Primary_Review_Month__c, "January"), 01/01/2017

I have tried CASE statements and IF statements and I have tried concatanating numerical values and all my attempts have all failed.
Any suggestions or something like this?
 
Best Answer chosen by Cullen Combs 1
Shruti SShruti S
You can use CASE function to achieve this and the DATE function to input a date. Here is how it should look like - 
CASE( 
    Primary_Review_Month__c, 
    'January',      DATE( 2017, 01, 01 ),
    'February',     DATE( 2017, 02, 01 ),
    'March',        DATE( 2017, 03, 01 ),
    'April',        DATE( 2017, 04, 01 ),
    'May',          DATE( 2017, 05, 01 ),
    'June',         DATE( 2017, 06, 01 ),
    'July',         DATE( 2017, 07, 01 ),
    'August',       DATE( 2017, 08, 01 ),
    'September',    DATE( 2017, 09, 01 ),
    'October',      DATE( 2017, 10, 01 ),
    'November',     DATE( 2017, 11, 01 ),
    'December',     DATE( 2017, 12, 01 ),
    NULL
)

All Answers

Karan Shekhar KaulKaran Shekhar Kaul

Hi Cullen,
Use DATE function.


CASE( Primary_Review_Month__c, 
'January', DATE(2017,01,01),
'february', DATE(2017,02,01),
'march', DATE(2017,03,01)
and so on......

TODAY()
)

Shruti SShruti S
You can use CASE function to achieve this and the DATE function to input a date. Here is how it should look like - 
CASE( 
    Primary_Review_Month__c, 
    'January',      DATE( 2017, 01, 01 ),
    'February',     DATE( 2017, 02, 01 ),
    'March',        DATE( 2017, 03, 01 ),
    'April',        DATE( 2017, 04, 01 ),
    'May',          DATE( 2017, 05, 01 ),
    'June',         DATE( 2017, 06, 01 ),
    'July',         DATE( 2017, 07, 01 ),
    'August',       DATE( 2017, 08, 01 ),
    'September',    DATE( 2017, 09, 01 ),
    'October',      DATE( 2017, 10, 01 ),
    'November',     DATE( 2017, 11, 01 ),
    'December',     DATE( 2017, 12, 01 ),
    NULL
)
This was selected as the best answer
Cullen Combs 1Cullen Combs 1

Thank you, both!

I went with something similar but I also wanted it to dynamically change from year to year:

CASE( 
    Primary_Review_Month__c, 
    'January',      DATE( YEAR(TODAY() + 365), 01, 01 ),
    'February',     DATE( YEAR(TODAY()), 02, 01 ),
    'March',        DATE( YEAR(TODAY()), 03, 01 ),
    'April',        DATE( YEAR(TODAY()), 04, 01 ),
    'May',          DATE( YEAR(TODAY()), 05, 01 ),
    'June',         DATE( YEAR(TODAY()), 06, 01 ),
    'July',         DATE( YEAR(TODAY()), 07, 01 ),
    'August',       DATE( YEAR(TODAY()), 08, 01 ),
    'September',    DATE( YEAR(TODAY()), 09, 01 ),
    'October',      DATE( YEAR(TODAY()), 10, 01 ),
    'November',     DATE( YEAR(TODAY()), 11, 01 ),
    'December',     DATE( YEAR(TODAY()), 12, 01 ),
    NULL
)

For January, I had to create a 'Next Year' function with YEAR(TODAY() + 365)
Is there a more elegant way of doing this?

Thanks, again!