Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
sfdc dev 2264sfdc dev 2264 

Salesforce formula to convert text to date

Hi ,

I need help on the following requirement as follows,

I have a text field called "Latest date" which has values in "March -2017" format

I want another formula field to convert the above text to dd/mm/yyyy format


The date can be 1st of every month

I shouldnt make any changes to the existing field and need this functionality in  a new field

Help me how to acheive this

Thanks in Advance
Best Answer chosen by sfdc dev 2264
GulshanRajGulshanRaj
Try this 
"01/"+CASE(UPPER(LEFT(Latest_date__c, FIND("-", Latest_date__c)-1)),
 "JANUARY","01",
"FEBRUARY","02",
"MARCH", "03",
"APRIL", "04",
"MAY", "05",
"JUNE","06",
"JULY","07",
"AUGUST","08",
"SEPTEMBER","09",
"OCTOBER","10",
"NOVEMBER","11",
"DECEMBER","12","0")+"/"+RIGHT(Latest_date__c,4)

 

All Answers

Rakesh Thota 15Rakesh Thota 15
Hi,

You need a REGEX function, please try the below 
AND(
NOT(ISBLANK(Custom_field__c)),
 NOT(
   OR(
     LEN (Custom_field__c)) = 10,
     REGEX(Custom_field__c), "[0-3]{1}[0-9]{1}/[0-1]{1}[0-9]{1}/[1-9]{1}[0-9]{3}")
     )
   )
)
May I suggest you please check with below links which might help you. 
  1. https://success.salesforce.com/answers?id=906300000019L9kAAE
  2. https://salesforce.stackexchange.com/questions/15098/how-to-convert-a-text-to-datetime

Hope this code and links will help you to fix your issue. make it solved if its works for you. 


Thanks,
Rakesh Thota.
 
sfdc dev 2264sfdc dev 2264
Hi,

I tried the above formula by creating a new formula date field but got the below error

REGEX may not be used in this type of formula
GulshanRajGulshanRaj
Hi,

Try this formula field having return type as text 
"01/"+CASE(UPPER(LEFT(Latest_date__c, FIND("-", Latest_date__c)-1)),
 "JANUARY","01",
"FEBRUARY","02",
"MARCH", "03",
"APRIL", "04",
"MAY", "05",
"JUNE","06",
"JULY","07",
"AUGUST","08",
"SEPTEMBER","09",
"OCTOBER","10",
"NOVEMBER","11",
"DECEMBER","12","0")+"/"+RIGHT(Latest_date__c, FIND("-", Latest_date__c)-2)

Here Latest_date__c is your custom field, so replace it with you field api name.

You can further convert it to date type.

All the best!!

Thanks
Gulshan Raj
 
sfdc dev 2264sfdc dev 2264
Hi gulshan

Thanks for the formula , its working for some values

for eg : March - 2017 its coming correctly as 01/03/2017

but if i give January -2017 it comes as 01/01/y-2017

 
GulshanRajGulshanRaj
Try this 
"01/"+CASE(UPPER(LEFT(Latest_date__c, FIND("-", Latest_date__c)-1)),
 "JANUARY","01",
"FEBRUARY","02",
"MARCH", "03",
"APRIL", "04",
"MAY", "05",
"JUNE","06",
"JULY","07",
"AUGUST","08",
"SEPTEMBER","09",
"OCTOBER","10",
"NOVEMBER","11",
"DECEMBER","12","0")+"/"+RIGHT(Latest_date__c,4)

 
This was selected as the best answer
sfdc dev 2264sfdc dev 2264
Thanks gulshan , it worked ,

you are a real genius

Thanks a lot