+ Start a Discussion
Mahesh Babu 187Mahesh Babu 187 

Convert Picklist Values to Date

Hi Team,
I have a requirement to convert my picklist value to date in a new field.
My picklist values are as May 2020, June 2021. Now I want to convert this value to a date format in the new field as 05/01/2020.
And all my values should be converted to 1st day of the month which is selected in the picklist.
Example:

Jan 2020-  01/01/2020
March 2021- 03/01/2021
April 2013 -  04/01/2013

It should be mm/dd/yyyy. And "dd" should be always 01.
i have searched many formulas to convert it to the required format, but could not achieve.
Please help me in achieving it.

Thanks,
Mahesh  
Best Answer chosen by Mahesh Babu 187
Andrew GAndrew G
Is it to be a Formula field or a normal Date field?

If formula date field you could try using the standard DATE formula
 
DATE( RIGHT(TEXT(PickListField),4), 
  CASE( LEFT(TEXT(PickListField),FIND(" ",TEXT(PickListField))-1),
    "January",01,
    "February",02
//and so on
  0),
  01
)
remember that DATE uses DATE(yyyy,mm,dd) to create it's date value - you can then format the field as required

If it's to be a text formula field, then using similar to above,
CASE( LEFT(TEXT(PickListField),FIND(" ",TEXT(PickListField))-1),
    "January","01",
    "February","02"
//and so on
  0) + "/01/" +
RIGHT(TEXT(PickListField),4)

if it is to be a writeable field, then use either of the above in a process builder

regards
Andrew
 

All Answers

Ajith Selvaraj 2Ajith Selvaraj 2
Hello Mahesh,
Can you say me how many picklist values are there?
Mahesh Babu 187Mahesh Babu 187
Hi Ajith,

I have 280 values in the picklist field.
Andrew GAndrew G
Is it to be a Formula field or a normal Date field?

If formula date field you could try using the standard DATE formula
 
DATE( RIGHT(TEXT(PickListField),4), 
  CASE( LEFT(TEXT(PickListField),FIND(" ",TEXT(PickListField))-1),
    "January",01,
    "February",02
//and so on
  0),
  01
)
remember that DATE uses DATE(yyyy,mm,dd) to create it's date value - you can then format the field as required

If it's to be a text formula field, then using similar to above,
CASE( LEFT(TEXT(PickListField),FIND(" ",TEXT(PickListField))-1),
    "January","01",
    "February","02"
//and so on
  0) + "/01/" +
RIGHT(TEXT(PickListField),4)

if it is to be a writeable field, then use either of the above in a process builder

regards
Andrew
 
This was selected as the best answer
Daniel Anthony 5Daniel Anthony 5
Fake ID ZONE Companies always with integrity,innovation, professional,service to win the trust of customers, we will abide by the integrity management, intentions services, to create value for customers!  fake id (https://www.idzone.ph/)