Salesforce DATE Function

Do you face any problem where you want to extract the date portion of the field in Salesforce and to solve this, do you want to explore the Salesforce DATE Function?

In this Salesforce tutorial, you will learn the Salesforce DATE Function. You will also explore the basic syntax of the function, use cases, and examples.

Furthermore, you will explore the steps of using the DATE function in Salesforce Lightning and Salesforce Classic with the real-based scenario.

What is DATE Function in Salesforce

DATE Function in Salesforce is one of the Date and Time functions that is used to work with Date fields. The DATE() function is extracted to extract the date portion from the year, month, and day values you enter.

In other words, it is used to create a date from a year, month, and day in Salesforce.

The following are the considerations that you must take care of:

  • Salesforce will show an error on the detail page if a formula field using the DATE function results in an invalid date, like February 29 in a year that’s not a leap year.
  • If you pass a date time field as a parameter to the function, you will get an error “Expected Date, received DateTime“, so make sure you will only pass the date fields.

Syntax of the Salesforce DATE Function

The Salesforce DATE() function has a straightforward and easy-to-understand syntax. Below is the syntax:

DATE(year,month,day)

Here:

  • year: The year parameter specifies the four-digit year of the date.
  • month: The month parameter specifies the two-digit month of the date.
  • day: The day parameter specifies the two-digit day of the date.

Note: Make sure there must be three parameters passed to the function if you have not passed all the parameters you will get an error “Incorrect number of parameters for function ‘DATE()’. Expected 3, received 1”.

Return: It returns the year, month, and day from the date field in Salesforce.

DATE Function Example in Salesforce

The table below provides simple examples illustrating how the DATE function works in Salesforce.

Input FieldReturn DATE()
DATE(2005, 01, 02)1/2/2005
DATE(2023, 12, 21)12/21/2023
DATE(2024, 02, 29)2/29/2024

In the “Input Field” column, you see the date entered by the user, and in the “Return” column, you see the output generated by the DATE function in Salesforce.

Now that you have got the basics of the DATE Function with the syntax of the function. You have also learned examples of how the DATE function works in Salesforce.

How to use the DATE Function in Salesforce Lightning

Here, you will go through a step-by-step implementation of how to use the DATE function in Salesforce Lightning.

There are various scenarios where you need the DATE function one of such that you want to create a complete date from the different components of the date. Below are the steps to achieve this scenario:

1. Login to your Salesforce account and redirect yourself to the main “Home Setup” page.

2. Once you reach click on the “Object Manager” option at the top navigation bar of the Lightning Interface.

Salesforce DATE Function

3. Search for the object in the “Quick Find” and from the filter list, click on it. For instance, you can use the “Contact” object.

Salesforce DATE Function Example

4. In step, click on the “Fields & Relationships” option. Then, click on the “New” button at the top on the right side of the page.

Salesforce Lightning DATE Function

5. Choose the field type that you need to define the function from the list of the given data types. After that, click on the “Next” button to proceed.

According to the scenario, you need to choose the “Formula” data type by clicking on the radio button.

Salesforce Lightning DATE Function Example

6. Enter the “Field Label” and when you click on the “Field Name” it will be automatically populated based on the field label. You can enter the field label as “Lucky Date Generator”.

Moving down choose the formula return type, from the list of the field list given below. Here, you need to choose the “Date” by clicking on the radio button corresponding to the date field.

DATE Function in Salesforce

7. Click on the “All Functions Categories” dropdown. Select the appropriate function category. In this scenario, choose the “Date and Time” category.

Search for the “DATE” function, and click on it to select the “DATE” function. Click on the “Insert Selected Function” button located in the functions section to insert the function into your formula.

Now, the function is successfully added to the formula subtab. Once it is added replace the function parameters with the date field as per the scenario.

For example, if you have separate number fields such as Year, Month, and Day and you decide to combine these fields and create a formula that generates a lucky date based on that.

The formula is as given below:

DATE(Favorite_Year__c, Favorite_Month__c, Favorite_Day__c)

Here is the detailed description of the formula:

Fields, Operator, and Function used in the formula:

  • Favorite_Year__c: It is a custom numerical field that stores the favorite year of the contact.
  • Favorite_Month__c: It is a custom number field that stores the favorite month of the contact.
  • Favorite_Day__c: It is a custom numerical field that stores the favorite month of the contact.
  • DATE(): It is a Salesforce function that creates a date.

Working on the formula:

  • DATE(Favorite Year, Favorite Month, Favorite Day): The DATE() function takes three parameters year, month, and day, and creates a date value based on it.

8. Click on the “Check Syntax” button. If there are any errors in your formula, you will receive an error message. Otherwise, you will see a message saying “No Syntax error in merge fields or function”.

Optionally, you can provide a “Description” and “Help Text” to provide more information about the formula. After completing these steps, proceed by clicking the “Next” button.

DATE Function in Salesforce Example

9. In this step, you need to establish field-level security, it will grant or revoke access to the profile to whom you want to give edit access to the field.

  • If you want to give access to all profiles click on the “Visible” checkbox given at the top.
  • If you want to give access to some of the profiles, click on the checkbox corresponding to the profile name.
  • If you want to revoke the edit access of the profiles, click on the already-checked profiles to uncheck them.

To proceed, click on the “Next” step.

DATE Function in Salesforce Lightning

10. Select the page layouts on which you want to show up this field in Salesforce. All the page layouts are selected by default.

If you do not want to include the custom field on a particular layout, you can deselect that layout.

To complete the process of using the DATE() function in Salesforce, click on the “Save” button.

DATE Function in Salesforce Lightning Example

11. Once you have created the formula field, you can use it as follows:

  • Open the “Contacts” item in Salesforce and create new contacts.
  • Enter the fields “Favorite Year”, Favorite Month”, and “Favorite Day”. After that, save the contacts.
  • Go to the “Details Section” of the contact. You will find the “Lucky Date Generator” field that displays the result of the DATE function.

Let’s understand it with the example:

For example, if you enter a year, month, and day field with values “2014”, “09”, and “18” in the contact object. After the execution of the formula, you will get the output for the lucky date generator “09/18/2014”.

How to use the DATE Function in Salesforce Lightning

You have learned the concept of how to use the DATE function to create a date in Salesforce Lightning. In the next section, you will learn how to use the DATE function in Salesforce Classic.

How to use DATE Function in Salesforce Classic

The DATE function in Salesforce Classic can be useful in various scenarios, such as calculating the upcoming lead anniversary date for the leads.

In the following steps, we will explore how to create a formula to fetch the date value in Salesforce Classic.

1. Log in to your Salesforce account using the Salesforce Classic Interface. At the top right side of the interface, click on the “Setup” option.

Salesforce Classic DATE Function

2. In the left sidebar, find the “Build” section. Locate the “Customize” dropdown within the “Build” section, you will see a list of Salesforce objects.

Select the specific object to which you want to utilize the DATE function. Click on the dropdown menu and expand the options in it from here select the “Fields” option.

According to the scenario, you can choose the “Leads” object.

Salesforce Classic DATE Function Example

3. Scroll down to the “Lead Custom Fields & Relationships” section. Click on the “New” button to proceed with the creation of a new field in Salesforce Classic.

DATE Function in Salesforce Classic

4. From the list of data types, choose the data type that the custom field will have by clicking on the radio button next to “Formula”. Then click on the “Next” button to proceed to the next step.

DATE Function in Salesforce Classic Example

5. Enter the “Field Label” for the new custom formula field. In this scenario, you can enter “Upcoming Lead Anniversary”. After entering the field label, click on “Field Name”, and it will be automatically generated based on the field label.

Scroll down to the formula return type and select the appropriate data type for the formula return type. In this scenario, choose the “Date” as the data type by clicking on the radio button.

Click on the “Next” button to continue to the next step.

How to use DATE Function in Salesforce Classic

6. Select the category of the function from the “All Function Categories” dropdown. In this scenario, click on the “Date and Time” category.

Choose the specific function from the category by clicking on it. According to the scenario, you click on the “DATE” function.

Click on the “Insert Selected Function” button located below the function section to insert the function into the sub-tab.

Now, replace the parameters with the appropriate fields and operators to create the desired formula. In this case, you calculate the upcoming lead anniversary date based on the lead created date and the year of relation with the lead. Below is the formula:

DATE(Year(Created_Date__c) + Year_of_Realtionship__c, Month(Created_Date__c), Day(Created_Date__c))

Here is the detailed description of the formula:

Fields, Operator, and Function used in the formula:

  • Created_Date__c: It is a custom date field type that stores the created date in Salesforce.
  • Year_of_Realtionship__c: It is a custom numeric field type that stores the year of relation with Salesforce Lead.
  • DATE(): It is a function that creates a date in Salesforce.
  • YEAR(): It is a function that extracts the year in Salesforce.
  • MONTH(): It is a function that extracts the month from the date in Salesforce.
  • DAY(): It is a function that extracts the day from the date in Salesforce.

Working on the formula:

  • Year(Created_Date__c) + Years_of_Realtionship__c: Here you add the number of years of the relationship of the lead to the year of the created date of the lead.
  • DATE(Year(Created_Date__c) + Years_of_Realtionship__c, Month(Created_Date__c), Day(Created_Date__c)): Here you create a date using the year fetched from the created date using the year() function, month fetched from the created date using the month() function, and day fetched from created date using the day() function.

7. To verify the formula, click on the “Check Syntax” button. If there is an error you will get an error message. If there are no errors, you will see the message “No Syntax error in merge fields or function”.

Optionally, you can enter a “Description” and “Help Text” to provide additional information about the formula. After this, click on the “Next” button to continue.

How to use DATE Function in Salesforce Classic Example

8. In this step, you need to set up field-level security for the custom formula field.

Choose the profiles to which you want to provide editing access to the field. Some profiles are already selected by default. To quickly select all profiles at once, click on the “Visible” checkbox.

Then, click on the “Next” button, to proceed to the next step.

Use DATE Function in Salesforce Classic

9. Select the page layout where you want to include this field. The field will be added as the last field in the first two-column section of the page layout.

By default, all page layouts are selected. To finish the process of creating a formula field using the DATE() function in Salesforce, click on the “Save” button.

Use DATE Function in Salesforce Classic Example

10. After you have created the formula field, you can use it as follows:

  • Click on the “Leads” item in Salesforce Classic. Create a new lead and save it.
  • Open the lead you have created.
  • Navigate to the “Details Section” and locate the “Upcoming Lead Anniversary” field. This is where you will see the output of the DATE function based on the formula you defined.

Let’s understand it with the example:

For example, if you created a lead on the “11/08/2020″ field with values in the lead object. After executing the formula, you will get the output for the upcoming lead anniversary after 5 years of lead creation as “11/08/2020”.

Example of Salesforce DATE Function

You have learned how to use the DATE function in Salesforce Classic.

Conclusion

It is possible to create a date using the DATE() function in Salesforce. Through this article how to use the DATE function, its syntax, and various use cases.

In addition, you will learn a step-by-step guide on how to DATE function in both Salesforce Lightning and Salesforce Classic.

You may like to read: