1

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?

2

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

  • Thank you for the response. But I was looking for a formula field where its return type would still be date? I was hoping it would be in the format like 01/31/2015. – sfDev123 Oct 26 '15 at 8:43
  • 1
    I think that's not possible. it will be as per user Locale – C0DEPirate Oct 26 '15 at 9:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.