Print this page

Formula to change date format

Knowledge Article Number 000123532
Description I would like to see a date displayed in a different format. For example I have the Opportunity Close Date and it looks like this 8/16/2012 and I would like to see it like this Aug 16, 2012.
Resolution
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:
  1. Click on your name | setup
    • (For Standard Objects): App Setup | Customize | go to the desired object where the field is located | Fields | New custom field
    • (For Custom Objects): Create | Custom Objects | Click on the name of the object where the field is located | Fields | New custom field
  2. Select the Data Type as Formula and click Next
  3. Give a Field Label and Field Name
  4. Select the Formula Return Type as Text and click Next
  5. Type one of the sample formulas given below (as per your requirement), within the box and Click Next
  6. Establish the Field Level Security and click Next
  7. 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 ))


ResultIf Date value in the date field is 5/7/2015, the result in this formula field would be Sunday, July 5, 2015

 
For more information on such formulas, refer to Common Date Formulas




promote demote