+ Start a Discussion
dietcoladietcola 

change date to MM/DD/YYYY format in formula editor

Hello, all-

 

One of my field updates uses the TEXT(Date__c) function to convert the date into text.  It prints it in the YYYY-MM-DD format, though, which means it's the only date in our entire Salesforce system that is not in the MM-DD-YYYY format.

 

Is there a way to customize that within the formula editor?

 

You're all life-savers; thank you as always!

Best Answer chosen by Admin (Salesforce Developers) 
dietcoladietcola

Woo-hoo!  This is the best forum day ever.

 

I had to make one tweak and remove the DATEVALUE part, which I'm just posting in case anyone ever needs to refer to this forum in the future.  All credit to shillyer/Sati, though.  =)

 

TEXT(MONTH(Date__c))+"/" +TEXT(DAY(Date__c))+"/" +TEXT(YEAR(Date__c))

 

Thanks so much!!


 

All Answers

shillyershillyer

Try this:

 

 

TEXT(MONTH(DATEVALUE(Date__c)))+"/" +TEXT(DAY(DATEVALUE(Date__c)))+"/" +TEXT(YEAR(DATEVALUE(Date__c)))

 

Hope that helps,

Sati

 

dietcoladietcola

Woo-hoo!  This is the best forum day ever.

 

I had to make one tweak and remove the DATEVALUE part, which I'm just posting in case anyone ever needs to refer to this forum in the future.  All credit to shillyer/Sati, though.  =)

 

TEXT(MONTH(Date__c))+"/" +TEXT(DAY(Date__c))+"/" +TEXT(YEAR(Date__c))

 

Thanks so much!!


 

This was selected as the best answer
Fraidy StraussFraidy Strauss
6 years later and this is still being helpful! Thank you!
usersfdc21usersfdc21
Thank you Shillyer and Dietcola...it was very helpful to me.
Mikel de DiegoMikel de Diego
If you really need MM-DD-YYYY format better try.

IF( LEN(TEXT(MONTH(date__c))) > 1 , TEXT(MONTH(date__c)), "0" + TEXT(MONTH(date__c))) + "-" + IF(LEN(TEXT(DAY(date__c))) > 1 , TEXT(DAY(date__c})), "0" + TEXT(DAY(date__c))) + "-" + TEXT(YEAR(date__c))

I find the solution looking for it here :)
Caleb Smith 13Caleb Smith 13
Same here, still helping 9 years later!
ferenikferenik
Now it should be
LPAD(TEXT(MONTH  (date__c)),2,"0")&"/ "&LPAD(TEXT(DAY (date__c)),2,"0")&"/"&TEXT(YEAR(date__c)) 
Ken Li 14Ken Li 14
9 years later...
 
Greg Hart 7Greg Hart 7
This worked great for me as well.  Thanks Ferenik.
I did remove the extra space after ther first forward slash.
LPAD(TEXT(MONTH  (date__c)),2,"0")&"/"&LPAD(TEXT(DAY (date__c)),2,"0")&"/"&TEXT(YEAR(date__c)) 
Brad GregoryBrad Gregory
I have formula fields pulling a date from the account fields. They all come out yyyy-mm-dd. TEXT(Account.IT_Set_Up_Target__c) is one formula I'm using. I'm not sure what to add to that to have the date format be correct. Thanks for the help. 
Jason HollingsworthJason Hollingsworth
@ferenik: 
ferenik
BEST ANSWER (imho)

Now it should be
LPAD(TEXT(MONTH  (date__c)),2,"0")&"/ "&LPAD(TEXT(DAY (date__c)),2,"0")&"/"&TEXT(YEAR(date__c)) 

This was huge for my project!!! I have been scowering search engines for a way to get DataLoader to spit out MM/DD/YYYY format, and being that the original question was for MM/DD/YYYY and not M/D/YYYY, it should be the BEST ANSWER. I respect the first, but this is not complicated as everything resists issues I thought we all worked to solve at the turn of the 21st century with Y2K. Not even Excel will convert to this format without special coding. Zero's are super duper important. It's nuts!!!
Jason HollingsworthJason Hollingsworth
ALSO...I ran into a secondary issue after using this: if there is no populated date in the field you are mirroring you wind up with "//" in the recieving field. I rectified this by nesting it in an IF statement that populates NULL if there is no date to mirror:

IF(ISBLANK( date__c), NULL, LPAD(TEXT(MONTH (date__c)),2,"0")&"/"&LPAD(TEXT(DAY (Date_of_Loss__c)),2,"0")&"/"&TEXT(YEAR(date__c)))
Thomas Osborne 2Thomas Osborne 2
Fenrick's worked great! Actually found the "/ /" useful in our circumstance :-). But also handy to know IF(ISBLANK... is an option too.
Maria Krueger 3Maria Krueger 3
These solutions are still helping other Admins! Fenrick's is what I used, but I am keeping these all as options in the future.
Taylor HatchellTaylor Hatchell
I am trying to use the formula provided above but I am getting an error. Any help would be great? The error says I am missing a ) but I can't figure out if I really am or if it is something else.

IF(ISPICKVAL(Account.Division__c,"Access Perks"), LPAD(TEXT(MONTH  (Account.Launch_Date__c)),2,"0")&"/ "&LPAD(TEXT(DAY (Account.Launch_Date__c)),2,"0")&"/"&TEXT(YEAR(Account.Launch_Date__c))

I need to pull the date if the picklist = Access Perks. I had it working but my date as yyyy/mm/dd
Siri Deva Singh KhalsaSiri Deva Singh Khalsa
12 years later. Still working. I used the "Best Answer" as it was appropriate for my use case.
Mr.HarryMr.Harry
@ferenik: What date__c field is this?? what value stores in this
Mr.HarryMr.Harry

Here, In TEXT(TODAY()) I'm getting date format in YY/DD/MM but i want to give date format as MM/DD/YY Format & below is my formula field.

TEXT(TODAY())& " " &':'& " "&[Contact].XYZ__c & BR() &
PRIORVALUE([Contact].XYZ__c )

Thanks in advance!