How do I change the format of a date in formula field? I want its data type to be a date. I want to change its format to 'MM-dd-YYYY' but I don't want to change user's locale settings. Is this possible?
[SalesForce] Change Date Format in Formula Field
Related Solutions
Dataloader.io has an advanced settings pain on the run page of their wizard.
You can change the expected day-month order here.
Most Salesforce ETL tools have some setting like this.
If that doesn't fix it, then it is likely that something actually wrong with the date format itself, such as the delimiters between the different date elements not being acceptable, or other characters corrupting the date.
For reference here is the full settings pain:
According to the help docs you will need your format to be:
DATEVALUE( "YYYY-MM-DD" )
You can also convert text to a Date so you can use the string value with your other Date fields and formulas. You’ll want your text to be formatted as “YYYY-MM-DD”...
If you are only needing the year from the existing text then you can use the RIGHT function described here.
RIGHT(Date_Key__c, 4)
Related Topic
- [SalesForce] Formula – Format Date
- [SalesForce] Date format from Apex variable in VF
- [SalesForce] is there anyway to get datetime field in format dd/mm/yyyy hh:mm:ss format using formula field
- [SalesForce] invalid date – When changed date format from US to UK
- [SalesForce] How to change date format depends on user locale
- [SalesForce] Change date format in formula to MM/DD/YY
Best Answer
Please go through below and play around with it.
Formula to change date format
As per standard functionality, we cannot modify the Date field in such a format (to include the text value of Month such as Jan/January, Feb/February etc.). However, the same can be achieved by creating a custom formula field by following the steps given below:
Select the Data Type as Formula and click Next Give a Field Label and Field Name Select the Formula Return Type as Text and click Next
Type one of the sample formulas given below (as per your requirement), within the box and Click Next Establish the Field Level Security and click Next
Add the field to the Page Layouts as needed and click Save
Sample Formula 1: To show the Date in European Date Format
text(DAY( xxxxxxx )) +' '+ CASE( MONTH( xxxxxx ) , 1, "Jan", 2, "Feb", 3, "Mar", 4, "Apr", 5, "May", 6, "Jun", 7, "Jul", 8, "Aug", 9, "Sep", 10, "Oct", 11, "Nov", "Dec") +', '+ Text(YEAR( xxxxxxx ))
Result: If Date value in the date field is 5/7/2015, the result in this formula field would be 5 Jul, 2015
Sample Formula 2: To show the Date in American Date Format
CASE( MONTH( xxxxxxxx ) , 1, "January", 2, "February", 3, "March", 4, "April", 5, "May", 6, "June", 7, "July", 8, "August", 9, "September", 10, "October", 11, "November", "December") +' '+ text(DAY( xxxxxxx )) +', '+ Text(YEAR( xxxxxxx ))
Result: If Date value in the date field is 5/7/2015, the result in this formula field would be July 5, 2015
NOTE: In both samples given above, replace the values, xxxxxxx with the required date field's API Name that you would like to be displayed in a different format. To replace xxxxxxx from the formula, simply click on Insert Field button and select the date field you want to insert. This will show the API name of the field which can be used to replace xxxxxxx. Similarly, if you want to include the text value of the day as well along with date, from the given field, you can use the following formula: CASE(MOD( CloseDate - DATE(1900, 1, 6), 7), 0, "Saturday", 1, "Sunday", 2,"Monday", 3, "Tuesday", 4, "Wednesday", 5, "Thursday", 6,"Friday","")+","+' '+ CASE( MONTH(CloseDate ) , 1, "January", 2, "February", 3, "March", 4, "April", 5, "May", 6, "June", 7, "July", 8, "August", 9, "September", 10, "October", 11, "November", "December") +' '+ text(DAY( CloseDate )) +', '+ Text(YEAR( CloseDate ))
Result: If Date value in the date field is 5/7/2015, the result in this formula field would be Sunday, July 5, 2015
Here is Link to Knowledge Article:
https://help.salesforce.com/apex/HTViewSolution?id=000123532&language=en_US