+ Start a Discussion
sfdc dev 2264sfdc dev 2264 

Formula text to date help needed

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
Satish PrajapatSatish Prajapat
Hello,
I am providing the apex code that can easily update the fields.
public class TextDate_to_Date 
{
    public String dateInTextFormat;
    public List<Credit_Card__c> ccObj;
    public Map<String, Integer> map1;
    public TextDate_to_Date()
    {
        dateInTextFormat = null;
        ccObj = new List<Credit_Card__c>();
        map1 = new Map<String, Integer>{'January'=>1, 'February'=>2,'March'=>3,'April'=>4,'May'=>5,'June'=>6,'July'=>7,'August'=>8,'September'=>9,'October'=>10,'November'=>11,'December'=>12};
    }
    public void m1()
    {
        ccObj = [Select Id, name, Date_Text_Type__c, Text_to_date_converted_value__c from Credit_Card__c];
        for(Credit_Card__c cc : ccObj)
        {
            String temp = '';
            temp = cc.Date_Text_Type__c;
            if(temp == null)
            {
                continue;
            }
            String month = temp.split(',')[0];
            String year = temp.split(',')[1];
            for(String mm : map1.keySet())
            {
                System.debug('mm : '+mm);
                if(mm == month.trim())
                {
                    month = map1.get(mm) +'';
                }
            }
            if(month != null && year != null )
            {
                String finalDate = month.trim()+'/'+'01'+'/'+year.trim();
                date requiredDateFormat = date.parse(finalDate);
                
                Credit_Card__c tempObjectForUpdation = new Credit_Card__c();
                tempObjectForUpdation.Id = cc.Id;
                tempObjectForUpdation.Text_to_date_converted_value__c = requiredDateFormat;
                
                update tempObjectForUpdation;
            }
        }
    }
}
Here Credit_Card__c is object where our fields presents.

 

All Answers

Satish PrajapatSatish Prajapat
Can we write the logic using Apex class or do you want to achieve the logic with the help of formula field.?
sfdc dev 2264sfdc dev 2264
any solution is fine with me 
Ankur Saini 9Ankur Saini 9
Hi,

try this one:
 
"01/" + IF(CONTAINS( LatestDate__c , "January" ), "01", IF(CONTAINS( LatestDate__c , "February" ), "02", IF(CONTAINS( LatestDate__c , "March" ), "03", IF(CONTAINS( LatestDate__c , "April" ), "04", IF(CONTAINS( LatestDate__c , "May" ), "05", IF(CONTAINS( LatestDate__c , "June" ), "06", IF(CONTAINS( LatestDate__c , "July" ), "07", IF(CONTAINS( LatestDate__c , "August" ), "08", IF(CONTAINS( LatestDate__c , "September" ), "09", IF(CONTAINS( LatestDate__c , "October" ), "10", IF(CONTAINS( LatestDate__c , "November" ), "11", "12"))))))))))) + "/" + RIGHT(LatestDate__c , 4)

Thanks,
Ankur Saini
http://mirketa.com
Lokesh KumarLokesh Kumar
Hi SFDC,

Assuming that your format will not change as "March-2017" use the below formula.
 
DATEVALUE(TEXT(VALUE(RIGHT(Latest_date__c,4)))&"-"&
TEXT(VALUE(CASE(LEFT(Latest_date__c, LEN(Latest_date__c)-5),
                    
                    "January","1",
                    "Febraury","2",
                    "March","3",
                    "April","4",
                    "May","5",
                    "June","6",
                    "July","7",
                    "August","8",
                    "September","9",
                    "October","10",
                    "November","11",
                    "December","12",
                    "3") ))
                    
&"-"&"1")

Lokesh
 
Satish PrajapatSatish Prajapat
Hello,
I am providing the apex code that can easily update the fields.
public class TextDate_to_Date 
{
    public String dateInTextFormat;
    public List<Credit_Card__c> ccObj;
    public Map<String, Integer> map1;
    public TextDate_to_Date()
    {
        dateInTextFormat = null;
        ccObj = new List<Credit_Card__c>();
        map1 = new Map<String, Integer>{'January'=>1, 'February'=>2,'March'=>3,'April'=>4,'May'=>5,'June'=>6,'July'=>7,'August'=>8,'September'=>9,'October'=>10,'November'=>11,'December'=>12};
    }
    public void m1()
    {
        ccObj = [Select Id, name, Date_Text_Type__c, Text_to_date_converted_value__c from Credit_Card__c];
        for(Credit_Card__c cc : ccObj)
        {
            String temp = '';
            temp = cc.Date_Text_Type__c;
            if(temp == null)
            {
                continue;
            }
            String month = temp.split(',')[0];
            String year = temp.split(',')[1];
            for(String mm : map1.keySet())
            {
                System.debug('mm : '+mm);
                if(mm == month.trim())
                {
                    month = map1.get(mm) +'';
                }
            }
            if(month != null && year != null )
            {
                String finalDate = month.trim()+'/'+'01'+'/'+year.trim();
                date requiredDateFormat = date.parse(finalDate);
                
                Credit_Card__c tempObjectForUpdation = new Credit_Card__c();
                tempObjectForUpdation.Id = cc.Id;
                tempObjectForUpdation.Text_to_date_converted_value__c = requiredDateFormat;
                
                update tempObjectForUpdation;
            }
        }
    }
}
Here Credit_Card__c is object where our fields presents.

 
This was selected as the best answer
Satish PrajapatSatish Prajapat
Hello 'sfdc dev 2264',
run the above class with the help of trigger.
and if your problem is solved then set as a best answer.
sfdc dev 2264sfdc dev 2264
Thanks all 👍