Salesforce Formula Add Year to Date

In this Salesforce Tutorial, we will learn the formula to Add Year to Date in Salesforce. Moreover, we will go through the steps using which we can Add the Year to Date Field in Salesforce Lightning and Salesforce Classic.

In addition, we will go through one special case based on the requirements of the user. The special case is “Salesforce Formula Add Year To Date where the End Date should be one day before the Start Date only the Year will change”.

In Salesforce, there are many scenarios where we need to Add Year to Date in Salesforce, such as Sales Forecasting, Contract Management, Project Planning, Product Expiry, License Warranty, etc.

In all these situations, we need to Add Year to Date in Salesforce. So, If you want to explore How to Add Year to Date in Salesforce Lightning and Classic Edition, you have come to the right place.

The following are the topics that we will cover in this Salesforce Tutorial:

  • Salesforce Date Field
  • Salesforce Day Function
  • Salesforce Month Function
  • Salesforce Year Function
  • Salesforce Date Function
  • How to add Year to Date in Salesforce
  • Add Year to Date using Advance Formula in Salesforce Lightning
  • Add Year to Date using Advance Formula in Salesforce Classic
  • Salesforce Formula Add Year To Date where the End Date should be one day before the Start Date only the Year will change.

Salesforce Date Field

In Salesforce, the Data Type is used to represent which type of data we can store in the field of an object. And Date is one of the standard data types in Salesforce.

It is used to store and manipulate the date of the record in a field of the object. To store data in the Date Field, we use the standard date format.

The Date Field Format is as given below:

YYYY-MM-DD

Here, YYYY represents the year in 4 digits, MM represents the month in 2 digits, and DD represents the day in 2 digits. For example, the Date Field is “2023-03-29” which represents March 29th, 2023.

With, this we have learned about Salesforce Date Field. Now, we will move ahead and learn about the different Date Field functions in Salesforce.

Salesforce Date Field Functions

In Salesforce, we have several functions related to Date Field. These functions are used to extract the Date, Month, Year, and Day from the Date.

Let’s discuss each of these functions briefly.

Salesforce Day Function

In Salesforce, the DAY() function is used to extract and return the day of the month from a date.

The syntax of the DAY function is as below:

DAY(date)

Salesforce Month Function

In Salesforce, the MONTH() function is used to extract and return the month of the year from a date.

The syntax of the MONTH function is as below:

MONTH(date)

Salesforce Year Function

In Salesforce, the YEAR() function is used to extract and return the year from a date.

The syntax of the YEAR function is as below:

YEAR(date)

Salesforce Date Function

In Salesforce, the DATE() function is used to create date values with year, month, and day values.

The syntax of the DATE function is as below:

DATE(year, month, date)

With, this we have learned about different functions related to Salesforce Date Field. Now, we will move further and see the formula Add Year to Date in Salesforce.

Read Add Hour to DateTime Field in Salesforce

How to add Year to Date in Salesforce

In Salesforce, to add a specific number of Year in a Date value, we use the Salesforce Date() and Year() functions together.

Here is the syntax to add Year to Date in Salesforce:

DATE(YEAR(date_field) + No.of years,MONTH(date_field),DAY(date_field))

In this code, The API name of the date field we want to add a year to should be entered for the Date Field. This formula returns a new date that is one or more years after the original.

Let’s understand it in detail with the help of an example:

Let’s say we want to create a formula to determine the Expiry of the Product by adding 1 year to the Manufacturing Date. And, Here is the formula for this:

DATE(YEAR(Mfg_Date__c)+1, MONTH(Mfg_Date__c), DAY(Mfg_Date__c))

Moreover in this article, we will also learn to determine whether a year is a leap year and create a formula to add the year to date for this also.

With, this we have learned the formula to add year-to-date in Salesforce. Now, we will move further and see How to Add Year to Date in Salesforce Lightning.

Add Year to Date using Advance Formula in Salesforce Lightning

Here, are the steps to Add Year to Date using the Advance Formula in Salesforce Lightning.

Step-1: Log in to your Salesforce account and navigate to Setup. On the left-hand menu, click on Object Manager.

Salesforce Formula Add Year to Date
Salesforce Formula Add Year-to-Date

Step-2: Click on the object for which we want to add a year-to-date. Here, I select the Product object from the list of objects.

Salesforce Formula Add Year to Date Example
Salesforce Formula Add Year-to-Date Example

Step-3: In the left-hand menu of the object page, click on Field & Relationship. Click on the New button to create a new Field and Relationship.

Salesforce Classic Formula Add Year to Date
Formula Add Year-to-Date

Step-4: Select the field type. Here, I select the Formula as the field type. Click Next, to continue.

Salesforce Classic Formula Add Year to Date Example
Formula Field in Salesforce Lightning

Step-5: Enter a Field Label for the new field, and the Field Name will be automatically populated. Here, I enter the Field Label as Product Mfg. Select Date as the Formula Return Type, and click Next.

Salesforce Add Year to Date
Salesforce Add Year to Date of Product Expiry

Step-6: In the Advance Formula editor, enter the formula to Add Year to Date. Here, is the formula copy and paste it:

IF(
    MONTH(Product_Mfg__c)=2 && DAY(Product_Mfg__c)=29,
    DATE(YEAR(Product_Mfg__c)+1, 2, 28),
    DATE(YEAR(Product_Mfg__c)+1, MONTH(Product_Mfg__c),                   DAY(Product_Mfg__c))
     )
  • Here, we first check whether the Product Mfg is February with respect to the year given. If it is, it will add one year to the date and return February 28 of the particular year.
  • Otherwise, add one year to the Product Mfg date year to exactly calculate the date with 1 year of addition.

Note: Replace the Product_Mfg__c field with the API name of the date field that you want to modify.

Step-7: To make sure the formula is valid, click Check Syntax. Optionally, we can add the Description, and Help Text and specify how you want to handle the blank field. Click Next, if the syntax is correct.

Add Year to Date using Advance Formula in Salesforce Lightning Example
Add Year to Date using Advance Formula

Step-8: Choose the suitable field-level security. Here, I checked the Visible checkbox to make it visible to all. Click Next.

Salesforce Lightning Add Year to Date
Salesforce Lightning Add Year-to-Date

Step-9: Choose the Page Layout option and click Save.

Salesforce Classic Add Year to Date
Salesforce Classic Add Year-to-Date

Step-10: Once the formula is created, open the Products object create a new product with the Product Mfg value, and save it. After that, click on the Details section and here we will get the Product Expiry field with the date value having one more year.

Add Year to Date using Advance Formula in Salesforce Lightning
Advance Formula in Salesforce Lightning

With this, we have learned to add the Year to Date field using the advance formula in Salesforce Lightning. Next, we will see how we can add the Year to Date field using the advance formula in Salesforce Classic.

Add Year to Date using Advance Formula in Salesforce Classic

Here, are the steps to Add Year to Date using the Advance Formula in Salesforce Classic.

Step-1: Log in to your Salesforce Classic account. Click on Avtar and then select Switch to Salesforce Classic.

Add Year to Date using Advance Formula in Salesforce Classic
Switch to Salesforce Classic

Step-2: Go to the Setup menu in Salesforce Classic.

Add Year to Date using Advance Formula in Salesforce Classic Example
Add Year to Date using Advance Formula

Step-3: Click on the Build section. Then, under the Customize section select the Object for which we want to add Year to the Date field. Here, I select the Campaign object from the list of objects. Click on the dropdown arrow and select Fields.

Add Year to Date in salesforce classic
Build<Customize<Campaigns<Field

Step-4: Scroll down to the page and click on the New button under Custom Fields & Relationships.

Add Year to Date in salesforce classic Example
Add Year to Date in Salesforce Classic Example

Step-5: Select the field type. Here, I select the Formula as the field type. Click Next, to continue.

salesforce classic example add year to date
Add Year to Date using Advance Formula

Step-6: Enter a Field Label for the new field, and the Field Name will be automatically populated. Select Date as the Formula Return Type, and click Next.

add year to date using salesforce classic
Add Year to Date using Salesforce Classic

Step-7: Enter the Add Year to Date formula into the Advance Formula editor. This is the formula to copy and paste:

IF(
    MONTH(Campaign_Start_Date__c)=2 && DAY(Campaign_Start_Date__c)=29,
    DATE(YEAR(Campaign_Start_Date__c)+1, 2, 28),
    DATE(YEAR(Campaign_Start_Date__c)+1, MONTH(Campaign_Start_Date__c),                      DAY(Campaign_Start_Date__c))
     )
  • Here, we first determine whether the given year is February corresponds to the Campaign_Start_Date__c. If so, it will return on February 28 of that specific year and add one year to the date.
  • In every other case, we must add a year to the Campaign Start Date year in order to determine the date exactly with a 2-year addition.

Note: Replace the Campaign_Start_Date__c field with the API name of the date field that you want to modify.

Step-8: Click Check Syntax to confirm that the formula is correct. Optionally, we can specify how to handle the blank field and provide a description and help text. If the syntax is right, click Next.

add year to date using salesforce classic example
Add Year to Date Using Salesforce Classic Example

Step-9: Choose the appropriate field-level security. To make it visible to all, I selected the Visible checkbox here. Click Next.

using salesforce classic add year to date
Field Level Security in Salesforce Classic

Step-10: Click Save after selecting the Page Layout option.

using salesforce classic example add year to date
Example Add Year-to-Date

Step-11: Open the Campaigns object when the formula has been created, add a new campaign with the Campaign Created value, and then save it. After that, click the Details button to access the Upcoming Campaign Date, where the date value has been extended by one year.

salesforce classic add year to date using advance formula
Salesforce Classic Add Year to Date using Advance Formula

From the above screenshot, we have observed that if the start date is 29 Feb of leap year and if we add one year to the date it will be evaluated as of 28 Feb of next year.

As a result, we now know how to use Salesforce Classic’s advanced formula to add the Year to Date.

Special Case: Salesforce Formula Add Year To Date where the End Date should be one day before the Start Date only the Year will change.

Scenario: Suppose you are working in a company that uses Salesforce, and you want to calculate the expiry date of the order. The expiry date should be based on the date the order starts date and the number of years the order is valid.

Below are the steps given to solve the above-stated scenario in Salesforce Classic:

Step-1: Log in to the Classic Salesforce interface. If you are automatically signed into the Lightning experience, switch to Salesforce Classic from the dropdown menu after clicking on the “Profile” button.

Add Year to Date using Advance Formula in Salesforce Classic
Switch to Salesforce Classic

Step-2: After logging into Salesforce Classic, select “Setup” from the menu at the top right of the page.

Add Year to Date using Advance Formula in Salesforce Classic Example
Add Year to Date using Advance Formula

Step-3: Locate the “Build” section on the left side of the page by scrolling down. Then, click on the “Customize” option and select the object on which you wish to add year-to-date with a special case. Next, select “Fields” from the pick object selection menu.

According to the scenario, search for the “Orders” object, click on it, and move to its field page.

Salesforce Formula Add Year To Date where the End Date one day before Start Date

Step-4: In this step, to add a new custom field, go to the bottom of the page and select the “New” button under the “Orders Custom Fields & Relationship”.

Salesforce Formula Add Year To Date where the End Date one day before Start Date Example

Step-5: You are now on the “Choose the Field Type” page. Indicate the kind of data the custom field will hold in accordance with the scenario. According to the scenario, you need to create a formula, therefore, the formula data type is necessary for this, so select the radio button next to the “Formula” data type. After that, click on the “Next” button and move to the next step.

Example of Salesforce Formula Add Year To Date where the End Date one day before Start Date

Step-6: Clicking the next button will take you to the “Choose Output Type” step. Enter the required data, such as “Field Label” and “Field Name”, first in this step, and then “Choose the formula return type” in accordance with the conditions.

In this case, I enter the field label as “Order Expiry Date”. After that when I click on the field name it automatically fills up and then I select the “Date” as the formula return type. Click the “Next” button to go to the next step after that.

Salesforce Classic Formula Add Year To Date where the End Date one day before Start Date

Step-7: In this step, you have to enter the formula according to your requirement. Click on the advanced formula subtab to use additional fields, operators, and functions. Choose salesforce functions from the “Functions” section, custom and standard fields from the “Insert Field” button, and operators from the “Insert Operator”.

IF(
    ISBLANK( EffectiveDate ),
    NULL,
    CASE(
        MOD(YEAR( EffectiveDate ) + Number_Of_Year_To_Add__c, 4),
        0,
        IF(
            AND(MONTH( EffectiveDate ) = 2, DAY( EffectiveDate ) = 29),
            DATE(YEAR( EffectiveDate ) + Number_Of_Year_To_Add__c, 2, 28),
            DATE(YEAR( EffectiveDate ) + Number_Of_Year_To_Add__c, MONTH( EffectiveDate ), DAY( EffectiveDate ))
        ),
        IF(
            OR(
                AND(MONTH( EffectiveDate ) = 2, DAY( EffectiveDate ) = 29),
                AND(MONTH( EffectiveDate ) = 2, DAY( EffectiveDate ) = 28)
            ),
            DATE(YEAR( EffectiveDate ) + Number_Of_Year_To_Add__c, 3, 1), 
            DATE(YEAR( EffectiveDate ) + Number_Of_Year_To_Add__c, MONTH( EffectiveDate ), DAY( EffectiveDate ))
        )
    ) - 1
)

Here is a detailed explanation of the formula:

  • We use the IF statement first, and in between use the ISBLANK() function, which checks whether the ‘EffectiveDate’ i.e. Order Start Date field is blank or not.
  • If the ‘EffectiveDate’ field is blank, the formula returns the result as NULL. Otherwise, the formula starts the procedure of date calculation which is defined below.
  • Then, we use the CASE statement, which checks whether the year of the ‘EffectiveDate’ field plus the number of years to add is a leap year i.e. multiple of 4 using the MOD() function.
  • If the year is leap year i.e. the result of the MOD function is 0, we use the IF statement, which checks if the ‘EffectiveDate’ field is 29th February.
  • If it is, the date becomes 28th February to handle the leap year. Otherwise, it will set the month as the same and the day as the ‘EffectiveDate’.
  • If the year is a non-leap year i.e. the result of the MOD function is not 0, we use the IF statement with the OR() function, which checks if the ‘EffectiveDate’ field is 29th February or 28th February.
  • If it is, the date is set to 1st March of the particular year. Otherwise, it will set the month as the same and the day as the ‘EffectiveDate’.
  • Last, we subtract the 1 from the calculated date, to get the one day before the start date.

Step-8: In this step, it’s time to verify whether the formula is correct or not. To do this, select the “Check Syntax” button from the menu. You will see the message “No syntax errors in merge fields or functions” if the formula is correct. You will see an error message if the formula has any mistakes.

You may also include the “Description”, “Help Text”, and “Handle the blank field” if you want. After that, click on the “Next” button to move to the next step.

Salesforce Classic Formula Add Year To Date where the End Date one day before Start Date Example

Step-9: Once you click on the next button, you will be on the “Establish Field-Level Security” step. Select the profiles to whom you want to provide edit access by using this field-level security. The field will be hidden from all profiles if field-level security is not set.

I simply click on the “Visible” checkbox as I grant access to all of the profiles. The next step is to click on the “Next” button.

How to add year to date in salesforce classic

Step-10: In this step, you are on the “Add to Page Layouts” page. Select the page layout that should include this field. The field will not appear on any page if you do not select a layout. After that finally, click on the “Save” button to save the formula field according to your requirement.

how to add year to date in salesforce classic example

Step-11: Once the formula field is created, you can use the formula field. Let’s see an example:

  • Open the “Orders” object, create a new object with the “Order Start Date” field, and save it.
  • After saving it, move to the order details page, You will see the “Order Expiry Date” that shows you the result of the formula that you have created.

Let’s understand it deeply:

Suppose, you have created an order with the order start date “29/2/2020” with the number of the year to add “5 years”. Now the formula will execute and show the order expiry date “28/2/2020”. It means that the formula effectively calculates add year to date where the order expiry date should be one day before the start date only the year will change.

example of add year to date in salesforce classic

As a result, we now know how to use Salesforce Classic’s advanced formula to add the Year to Date in special cases where the end date should be one day before the start date only the year will change.

You may also like:

Conclusion

In conclusion, we have learned about Salesforce Date Fied. In addition, we have also explored the Salesforce DATE, DAY, MONTH, and YEAR functions which are used to return the Date field.

Moreover, we have learned that both Salesforce Classic and Salesforce Lightning provide a simple procedure to Add the Year to Date Field.

In addition to this, the following are the topics that we have discussed:

  • Salesforce Date Filed
  • Salesforce Day Function
  • Salesforce Month Function
  • Salesforce Year Function
  • Salesforce Date Function
  • How to add Year to Date in Salesforce
  • Add Year to Date using Advance Formula in Salesforce Lightning
  • Add Year to Date using Advance Formula in Salesforce Classic
  • Salesforce Formula Add Year To Date where the End Date should be one day before the Start Date only the Year will change.