Salesforce Formula to get Last Day of Month

In this Salesforce Tutorial, we will learn the formula to Calculate the Last Day of the Month in Salesforce. Moreover, we will go through the steps using which we can get the last day of the month from a given date in Salesforce Lightning and Salesforce Classic.

While working in an organization that uses Salesforce as a CRM tool, I got a task where I need to display the last day of the month. So, to fulfill this requirement, I define an advance formula.

If you want to learn How to find the last day of the month from a given date using Salesforce Lightning and Salesforce Classic, you have come to the right place.

The following are the topics that we will cover in this salesforce tutorial:

  • Salesforce Formula Last Day of Month
  • Formula to detect last day of the month in Salesforce Lightning
  • Formula to detect last day of the month in Salesforce Classic

Salesforce Formula Last Day of Month

In Salesforce, there may be a need to get the last day of the month. For this, we will use the Salesforce Date(), Today(), Year(), and Month() functions together.

Here, is the general advance formula to find the last day of the month in Salesforce:

IF(MONTH(DateField)=12, DATE(YEAR(DateField),12,31), DATE(YEAR(DateField),MONTH(DateField)+1, 1)-1)
  • In this formula, we calculate the last day of the month of the date field passed in the API name of the date field DateField.
  • Here, we first check whether the month of the DateField is 12.
  • If it is, we use the DATE() function and return the year of the date value using the YEAR() function, while sets the month to 12, and the day to 31.
  • If not, we use the DATE() function and return the year of the date value using the YEAR() function, while the month of the date is returned using the MONTH() function, and we also increment it by +1. Then. we pass 1 as the day component.
  • Finally, we subtract the one to get the last day of the month.

With, this we have learned the Salesforce Formula Last Day of the Month. Now, we will move further and see How to get the Last Day of the Month in Salesforce Lightning.

Formula to detect last day of the month in Salesforce Lightning

Here, are the steps to find the last day of the month using the Advance Formula in Salesforce Lightning.

Step 1: Navigate to Salesforce Setup, by clicking on the Setup icon on the upper right side of the page.

Step 2: On the left side of the menu, click on Object Manager.

Salesforce Formula Last Day of Month
Salesforce Formula Last Day of Month

Step 3: Search for the object to whose field we want to get the last day of the month. Here, I select the Campaign object from the list of objects.

Salesforce Formula Last Day of Month Example
Salesforce Formula Last Day of Month Example

Step 4: Click on Fields & Relationships.

Step 5: Then, click on New.

Formula to detect last day of the month in Salesforce Lightning
Formula to detect last day of the month in Salesforce Lightning

Step 6: Choose the Formula as the field type.

Step 7: To continue, click Next.

Formula to detect last day of the month in Salesforce Lightning Example
Formula to detect last day of the month in Salesforce Lightning Example

Step 8: Enter a Field Label for the formula, and the Field Name will be automatically populated. Here, I enter Field Label as the Upcoming Campaign Date.

Step 9: Choose Date as the Formula Return Type. Then, click Next.

Formula to get last day of the month in Salesforce Lightning
Formula to get last day of the month in Salesforce Lightning

Step 10: Enter the advance formula to find out the last day of the month in the Advance Formula editor. Here, is the formula:

IF(MONTH(EndDate)=12, DATE(YEAR(EndDate),12,31), DATE(YEAR(EndDate),MONTH(EndDate)+1, 1)-1)
  • Here, we want to create a formula that finds the last day of the month and set it as the Upcoming Campaign Date. So, for this, we use the general advance formula of getting the last day of the month that we have discussed above in the first section.
  • In general, we are checking whether the month of the EndDate field is 12, if it is we are setting the day as 31, the month as 12, and the year the same as the EndDate field.
  • If not, we increment the month of the EndDate field by 1, set the year the same as the EndDate field, and the day as 1. Then, in order to determine the last day of the month, we finally deduct one day from it.

Step 11: Click Check Syntax to make sure the formula is correct.

Step 12: We can add the Description and Help Text as options, and we can also state how you want to handle the blank field.

Step 13: After that, click Next.

Formula to get last day of the month in Salesforce Lightning Example
Advance Formula to Get the Last Day of the Month

Step 14: Select the appropriate field Level Security and to make it visible to everyone, here we select the Visible checkbox.

Step 15: Select Next.

Get last day of the month in Salesforce Lightning
Get last day of the month in Salesforce Lightning

Step 16: Choose the Page Layout Name, for whom we want to add the formula field.

Step 17: To save the formula field, click Save.

Get last day of the month in Salesforce Lightning Example
Get last day of the month in Salesforce Lightning Example

We can use the formula field once we’ve created it. Let’s look at an example:

  • Create a campaign with the End Date field value by opening the Campaigns item and saving it.
  • After that, click on the Details section, and here the Upcoming Campaign Date field value will appear as the last day of the month.
Last day of the month in Salesforce Lightning Example
Last day of the month in Salesforce Lightning Example

As a result, we now know how to use Salesforce Lightning’s advanced formula to find the last day of the month. Next, we’ll look at how to use Salesforce Classic’s advance formula to detect the last day of the month.

Read Salesforce Formula DATETIMEVALUE

Formula to detect last day of the month in Salesforce Classic

Here, are the steps to detect the last day of the month using the Advance Formula in Salesforce Classic.

Step 1: From the Salesforce Lightning to open your Salesforce Classic edition. Click on Avtar and click Switch to Salesforce Classic.

Formula to detect last day of the month in Salesforce Classic
Switch to Salesforce Classic

Step 2: Click on the Setup option.

Formula to detect last day of the month in Salesforce Classic Example
Formula to detect last day of the month in Salesforce Classic Example

Step 3: Scroll down to the Build section. Then under Customize, select the object for whom we want to find the last day of the month. Here, I choose the Product from the object list.

Step 4: Click on the dropdown menu and select Fields.

Detect last day of the month in Salesforce Classic
Detect last day of the month in Salesforce Classic

Step 5: As you scroll down the page, click on the New button under Custom Fields & Relationships.

Detect last day of the month in Salesforce Classic Example
Detect last day of the month in Salesforce Classic Example

Step 6: Select the Formula as the field type.

Step 7: Click Next to proceed.

Formula to get last day of the month in Salesforce Classic
Formula to get last day of the month in Salesforce Classic

Step 8: Enter a Field Label for the formula, and the Field Name will be automatically populated. Here, I enter Field Label as the Payment Due Date.

Step 9: Choose Date as the Formula Return Type and then click Next.

Formula to get last day of the month in Salesforce Classic Example
Formula to get last day of the month in Salesforce Classic Example

Step 10: Enter the formula to calculate the last day of the month in the advance formula editor. The formula is as follows:

IF(MONTH(Purchase_Date__c)=12, DATE(YEAR(Purchase_Date__c),12,31), DATE(YEAR(Purchase_Date__c),MONTH(Purchase_Date__c)+1, 1)-1)
  • Here, we create a formula that finds the last day of the month and sets that date as the Payment Due Date. Therefore, for this, we use the same advance method for determining the month’s last day that we covered in the first section above.
  • In this formula, we figure out if the Purchase_Date__c field’s month is 12, and if it is, we set the day to 31 and the month and year to the same as the Purchase_Date__c field.
  • If not, we set the day to 1, the year to the same value as the Purchase_Date__c field, and increment the month to 1. Finally, in order to get the last day of the month we subtract 1 from the date value.

Step 11: To verify that the formula is valid, click Check Syntax.

Step 12: We can specify how you want to handle the empty field and include the Description and Help Text optionally.

Step 13: Then, click Next.

Get last day of the month in Salesforce Classic
Get last day of the month in Salesforce Classic

Step 14: Choose the appropriate Field’s Level Security, and then check the box Visible to make it visible to all.

Step 15: Click Next.

Get last day of the month in Salesforce Classic Example
Get last day of the month in Salesforce Classic Example

Step 16: Choose the Page Layout Name for which the formula field is to be added.

Step 17: Click Save to save the formula field.

Advance Formula to detect last day of the month in Salesforce Classic
Advance Formula to detect Last Day of the Month

Once we’ve defined the formula field, we may use it. Let’s explore an example:

  • Create a new product with the Purchase Date field by opening the Product object and saving it.
  • After that, in the Details section, will get the last day of the month.
Advance formula to detect last day of the month in Salesforce Classic Example
Advance formula to detect last day of the month in Salesforce

In a conclusion, we now understand how to calculate the last day of the month using Salesforce Classic’s advanced formula.

Also, Read

Conclusion

In conclusion, we have learned the advance salesforce formula to find the last day of the month.

Moreover, we have learned that both Salesforce Classic and Salesforce Lightning provide a simple procedure to calculate the last date of the month.

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

  • Salesforce Formula Last Day of Month
  • Formula to detect last day of the month in Salesforce Lightning
  • Formula to detect last day of the month in Salesforce Classic