Salesforce DATEVALUE Function

Do you want to learn about the Salesforce DATEVALUE Function in Salesforce?

If yes, in this Salesforce tutorial, you will learn the syntax, use cases, and examples of the DATAVALUE function.

Moreover, you will be guided through the step-by-step procedure of using the DATEVALUE function in both Salesforce Lightning and Salesforce Classic with real business-related scenarios.

DATEVALUE Function in Salesforce

The DATEVALUE function is one of the most useful functions of the Date and Time category that is used to work and manipulate the date and datetime fields in Salesforce.

In Salesforce, the DATEVALUE function is used to convert the text expression representing the datetime value or datetime field to a date value.

In simple words, you can say that it creates the date from its datetime value in Salesforce. It can be best suited in cases when you have imported the date/time data from external resources but you only need the date portion.

Warning!

If your org’s custom formulas include workarounds that adjust date values between 11:00 PM and 1:00 AM, remove them before enabling this setting. If you don’t remove the workarounds, your data could be inaccurate.

The following are the considerations of the DATEVALUE function in Salesforce, that you must keep in mind while working with this:

  • Field Validation: If the field referenced in a formula using date or time functions is not a valid text or date/time field, the formula field will display “#ERROR!”.
  • Date Entry Format: When entering a date in a formula, it should be surrounded by quotes and follow the format YYYY-MM-DD (year-month-day), where the year has four digits, the month has two digits, and the day has two digits.
  • Invalid Date Ranges: If the expression used in the formula doesn’t match valid date ranges, such as having the month (MM) outside the range of 01 to 12, the formula field will display “#ERROR!”. Salesforce enforces the date validation to make sure that the dates entered are within acceptable ranges.
  • User Time Zone and Coordinated Universal Time: Dates and times are always calculated using the user’s time zone, except in list views, reports, and related lists. In these cases, Salesforce uses Coordinated Universal Time (UTC) for consistency in data retrieval.

With this, you have grasped the knowledge of what is DATEVALUE function in Salesforce with its important considerations. Next, you will learn about the proper way of writing the function in Salesforce.

Syntax of DATEVALUE Function in Salesforce

The syntax of the DATEVALUE() function in Salesforce is as given below:

DATEVALUE(expression)

Here:

  • expression: The expression parameter specifies the text representing the date, date/time value, merge field, or formula expression that you want to convert to a date value.

Return: It returns the date value from the date/time field in Salesforce.

Now you have explored the syntax of the DATEVALUE function in Salesforce. Next, you will see the simple examples of the DATEVALUE function, so that you must know how the function works.

Salesforce DATAVALUE Function Examples

The table below provides simple examples explaining how the DATEVALUE function executes in Salesforce.

Input FieldReturn DATEVALU()
DATEVALUE(“2005-11-15”)11/15/2005
DATEVALUE(“8/1/2023, 1:14 PM”)#Error!
DATEVALUE(LastModifiedDate) where LastModifiedDate is 11/8/2023, 11:06 AM11/8/2023
DATEVALUE( Start_Date_Time__c )where Start_Date_Time__c is 4/4/2023, 12:40 PM4/4/2023
DATEVALUE(“1997-05-25”)5/25/1997

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

You have also learned examples of how the DATEVALUE function works in Salesforce. Next, you will explore step-by-step how to use the DATEVALUE function in Salesforce Lightning.

See also  PICKLISTCOUNT Function in Salesforce | Count Selected Values in Multi-select field in Salesforce

How to use DATEVALUE Function in Salesforce Lightning

Salesforce Lightning provides you with a powerful function to enhance data management when you are dealing with the date and date\time data.

Here you will be guided through the step-by-step process of implementing the DATEVALUE function in Salesforce Lightning, with a specific focus on one scenario.

One of the scenarios is you want to calculate the follow-up date for a campaign in Salesforce. Below are the steps for that:

1. Logging into the Salesforce account in the Lightning Interface and move to the “Main Home Setup” by clicking on the “Gear” icon located on the top right side, then click on the “Setup” option.

Salesforce DATEVALUE Function

2. Navigate to the “Object Manager” from the top navigation bar in the Lightning Interface.

Salesforce DATEVALUE Function Example

3. Select the object where you want to apply the DATEVALUE function in Salesforce. For example, according to the scenario, you can choose the “Campaigns” object.

Salesforce Lightning DATEVALUE Function

4. Once selected, proceed to the “Fields & Relationships” section. In this section, click on “New” to create a new field.

Salesforce Lightning DATEVALUE Function Example

5. Choose the “Formula” data type by clicking on the radio button next to it from the list of the field types. Then, click on the “Next” button to proceed to the next step.

DATEVALUE Function in Salesforce

6. In this step, assign the meaningful “Field Label” to the newly created formula field then click on the “Field Name” and it will automatically fill up based on the field label you entered.

According to the scenario, you enter the field label “Follow-Up Date”.

After that, scroll down and choose the formula return type from the list of data types. In accordance with the scenario, set the field type to “Date” by clicking on the radio button adjacent to it.

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

DATEVALUE Function in Salesforce Example

7. In this step you will be on the formula editor, click on the “All Functions Categories” dropdown. Choose the appropriate function category. As per the scenario, select the “Date and Time” category.

From the list of the functions, identify the “DATEVALUE” function, once you find the function click on it. Next, click on the “Insert Selected Function” button to insert the function into your formula.

After the function is successfully added to the formula subtab, replace the function parameters with the date/time field or text expression consisting of date.

For instance, you want to determine the follow-up date of the campaign based on the campaign created date. Your requirement is to add 10 days to the campaign created date, as you want a follow-up date after 10 days.

The formula for the above-situated scenario is as given below:

DATEVALUE(CreatedDate) + 10

Here is the detailed description of the formula:

Fields, Operator, and Function used in the formula:

  • CreatedDate: It is a standard date\time field that stores the date and time on which camping is created.
  • 10: It is a numerical value that you want to add to the created date of the campaign.
  • +: It is the + operator is used for addition
  • DATEVALUE(): It is a Salesforce function that converts the date/time field into a date-only value.

Working on the formula:

  • DATEVALUE(CreatedDate) + 10: Here, you will convert a date/time field into a date-only value i.e. it extracts only the date portion from the date/time “CreatedDate” field. Then, you add 10 days to obtain the date of the follow-up campaign.

8. Click the “Check Syntax” button to ensure your formula is free from errors. If there is no error, you will get the message “No Syntax error in merge fields or function”. If there are any errors in your formula, you will receive an error message.

See also  Distance() Function in Salesforce | Calculate distance between locations in Salesforce

In addition, you will get the “Description”, and “Help Text” to provide more information about the formula. After completing these steps, proceed by clicking the “Next” button.

DATEVALUE Function in Salesforce Lightning

9. In this step, establish field-level security by choosing the profiles that should have edit access to the new field. This step ensures data security and control.

  • If you want to revoke the edit access of the profiles, click on the already-checked profiles to uncheck them.
  • 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.

To proceed, click on the “Next” step.

DATEVALUE Function in Salesforce Lightning Example

10. Select the page layouts where the new field should be visible. By default, all page layouts are chosen, but you can customize this based on your requirements by unchecking the checkboxes.

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

How to use DATEVALUE Function in Salesforce Lightning

11. Once the formula is created, you can easily use it and see how it works.

  • Open the “Campaigns” item in Salesforce and create a new campaign.
  • Enter the necessary fields for the campaign and save the campaign once done.
  • Then, move on to the “Details Section” of the campaign. You will get the “Follow-Up Date” field that displays the result of the DATEVALUE function.

Let’s understand it with the example:

  • For instance, you created a new campaign on the “11/19/2022, 2:25 AM”. After the execution of the formula, you will get the output for the follow-up date “11/19/2022”.
  • You can effectively, say that the DATEVALUE function in Salesforce extracts the only date from the date/time field.
How to use DATEVALUE Function in Salesforce Lightning Example

With this, you have learned the concept of how to use the DATEVALUE function to convert the datetime field to date value only in Salesforce Lightning. In the next section, you will learn how to use the DATEVALUE function in Salesforce Classic.

How to use DATEVALUE Function in Salesforce Classic

Let’s learn step-by-step how to use the DATAVALUE function in Salesforce Classic.

You will learn the concept with the help of the scenario: Determine the age of a lead in the Classic Interface. The following are the steps:

Step-1: Access Salesforce Classic Setup

  • Signup to your Salesforce account in the Classic Interface.
  • Click on the “Setup” option located on the top right side of the page. If you don’t find the option there, click on your name dropdown, here you find the “Setup”, click on it.
Salesforce Classic DATEVALUE Function

Step-2: Object and Field Manager

  • Scroll down to the “Build” Section on the left side. Then, click on the “Customize” dropdown and expand the options.
  • After that, search for the object on which you want to utilize the function. As per the scenario, I choose the “Leads” object and click on the dropdown to expand the options.
  • Then, click on the “Fields” option from the list of options as you need to create the field where you will use the function.
Salesforce Classic DATEVALUE Function Example
  • You will get a list of the custom and standard fields. Scroll down to the “Lead Custom Fields and Relationships” section, and click on the “New” button to start up the process of creating a new formula field.
DATEVALUE Function in Salesforce Classic

Step-3: Field Type Selection

  • From the list of the data types, click on the “Formula” data type and click on the “Next” button to proceed.
  • Enter the meaningful “Field Label” for the new custom formula field. The “Field Name” will auto-fill based on the label. In accordance with the scenario, you can enter the field label “Lead Age Calculation”.
  • Moving down to the formula return type section, set the field type to “Number” by clicking on the radio button next to it and then click on the “Next” button.
How to use DATEVALUE Function in Salesforce Classic

Step-4: Formula Setup

  • In the formula editor, click on the “All Functions Categories” dropdown. Choose the “Date and Time” category.
  • Find the “DATEVALUE” function, and then click the “Insert Selected Function” button.
  • Replace the function parameters with the date/time field or date text expression. For example, if you want to calculate the age of a lead based on today’s date and the created date.
See also  COS() Function in Salesforce | Calculate cosine value in Salesforce

The formula is given as below:

TODAY() - DATEVALUE(CreatedDate)

Here is the detailed description of the formula:

Fields, Operator, and Function used in the formula:

  • CreatedDate: It is a standard date\time field in Salesforce that records the date and time when a lead was created.
  • DATEVALUE(): It is a Salesforce function used to extract the date portion from a date/time field.
  • TODAY(): It is a Salesforce function that returns the current date.
  • -: It is a Salesforce operator that is used for subtraction.

Working on the formula:

  • TODAY() – DATEVALUE(CreatedDate): Here, subtract the date component of the “CreatedDate” from the current date which results in a difference between these two dates in days representing the number.
  • Click the “Check Syntax” button to ensure your formula is error-free. Moreover, you can enter the “Description” and “Help Text” for additional information.
  • To proceed to the next step, click on the “Next” button.
How to use DATEVALUE Function in Salesforce Classic

Step-5: Field-Level Security

  • Establish field-level security by selecting profiles and granting the edit access to the new field. By default, some of the profiles are already selected.
  • You can uncheck profiles to revoke access or check the “Visible” checkbox to give access to all profiles.
  • To proceed to the next step of creating the formula field with function, click on the “Next” button.
How to use DATEVALUE Function in Salesforce

Step-6: Page Layouts

  • Choose the page layouts where the new field should be visible by clicking on the checkbox next to the page name. The field will be added as the last field in the first 2-column section of these page layouts.
  • The field will not appear on any page if you do not select a layout. Even, if you want to change the location of this field on the page, you need to customize the page layout.
  • Click “Save” to complete the process of using the DATEVALUE() function in Salesforce.
How to use DATEVALUE Function in Salesforce Example

Step-7: Testing the Function and Formula

  • Open “Leads” in Salesforce, create a new lead by entering the necessary details and save.
  • Then, move to the “Details Section” of the lead to see the “Lead Age Calculation” field displaying the result of the DATEVALUE function.

Example Scenario:

  • For instance, you create a new lead on “11/19/2022, 2:25 AM”. The formula will output “11/19/2022” as the lead age calculation. Hence you can say it effectively extracts only the date from the date/time field.
Example of DATEVALUE Function in Salesforce Classic

With this, you have grasped how the DATEVALUE function in Salesforce Classic extracts only the date from a date/time field.

Conclusion

In conclusion, this article provides a complete detail of the DATEVALUE function in Salesforce. The function allows us to extract the date value only from date/time fields.

We have got comprehensive guide on the DATEVALUE function definition, syntax, considerations, and practical examples.

Moreover, in the Lightning Interface of Salesforce, we have covered the process of implementing the function for calculating follow-up dates in campaigns.

Additionally, we acknowledge the importance and use of the DATEVALUE in Salesforce Classic with the practical example of calculating the age of the lead record.

You may like to read the following articles: