+ Start a Discussion
MinouMinou 

Converting a date format for use in Clicktools

I hope everyone's week is starting off well this Monday morning! 

 

I need help converting a date from MM/DD/YYY format to YYYY-DD-MM format.  Is there a way this can be accomplished with a formula field?

 

A little background on why I am asking, I am creating a call script using Clicktools.  The date format required by Clicktools is YYYY-DD-MM but the date format being passed from SF is MM/DD/YYY.  I have tried numerous formulas and they all result in errors.

 

Please help!  I am banging my head against the wall! This is day 2 of me trying to figure this out.

 

Thanks in advance for any advice you can offer.

 

Ashleigh

Best Answer chosen by Admin (Salesforce Developers) 
yudhvirmoryudhvirmor

TEXT(YEAR( Contract_End_Date__c ))&"-"&TEXT(MONTH(Contract_End_Date__c))&"-"&TEXT(DAY(Contract_End_Date__c))

All Answers

yudhvirmoryudhvirmor

Create Formula field of type Text and use LEFT, MID & RIGHT functions and concatenate them.

MinouMinou

I think that is the ONLY way I haven't tried to do it....LOL!

 

I will try that and let you know if it works.

 

Thanks!

MinouMinou

Ok, here is what I have accomplished so far......

 

I have created 2 formula fields one to convert the date field to text...... Contract End Date- Clicktools Conversion - TEXT(Available__c)

 

and another to attempt to get the date into the proper format .....Contract_End_Date_Format_for_Clicktools -  LEFT( Contract_End_Date_Clicktools_Conversion__c , 2)  &   MID( Contract_End_Date_Clicktools_Conversion__c , 1, 2)   &  RIGHT( Contract_End_Date_Clicktools_Conversion__c , 4)

 

I have a date 10/15/2010 after applying both of these formula fields this is the result I am getting....  20200-15 .....not the results I am looking for.... I need this date to be in the following format......   YYYY-DD-MM

 

I am almost there....so what am I doing wrong, please help!  Thanks, everyone!

yudhvirmoryudhvirmor

TEXT(YEAR( Contract_End_Date__c ))&"-"&TEXT(MONTH(Contract_End_Date__c))&"-"&TEXT(DAY(Contract_End_Date__c))

This was selected as the best answer
MinouMinou

IT WORKED!!!!!

 

Yay! I have beat my head against the wall for 2 days trying to figure this one out.  THANK YOU, THANK YOU, THANK YOU.  You have made my week!  :)

MalinMalin

Hi guys

 

This thread is quite old but hoping someone might be able to help as I am trying to do something similar.

 

Need to get a date field mapped in to Clicktools from Salesforce, and likewise map back if changed.

 

I want it to go between date fields, rather than one end being a text field.

 

Any ideas?

 

Thank you in advance!