Salesforce Formula to Get Last Day of The Month

In Salesforce, we sometimes need to determine the last day of a month based on a given date. For example, if a campaign has an End Date of 21st August 2025, we may want to know the 31st August 2025 (the last day of that month).

Salesforce does not provide a standard field to display a date like this, so we need a formula to calculate it. We can use a Salesforce Formula Field to find the last day of the month by using the date functions available in advanced formulas.

In this article, we will learn about how to get the last day of the month using a Salesforce formula. In this, I will create and explain a formula using functions that returns the last day of the month.

Salesforce Formula to Get Last Day of the Month

Below, I will explain the formula and the steps to get the last day of the month from any date in Salesforce. For this, we will use the Salesforce Date(), Today(), Year(), and Month() functions.

After creating the formula, when we provide any date, the formula will return the last date of the provided date’s month. Below, I have provided examples:

• End Date = 15-Aug-2025 -> Last Day of Month = 31-Aug-2025.
• End Date = 12-Feb-2024 -> Last Day of Month = 29-Feb-2024 (Leap Year).

1. Go to the Gear Icon. -> Click on the Setup. -> Select the Object Manager tab. Select the Object on which you want to create the Formula Field to extract the year from the date. Here, I’m going to create a Campaign Object.

• In the Campaign setup, click on the Fields and Relationships to create a new field and click the New button.

2. In this step, we will use a formula to calculate and display the last date of the month from the given date and display it on the campaign object. For that, select the option Formula from the data type and click Next.

3. Next, provide the Field Label and API Name for the formula field. Then, since we want to display the last date of the given date, in the  Formula Return Type, select Date and click on the Next button.

4. In the formula editor, enter the formula below and click the Next button.
   • Here, we want to use the DATE() function to check the year and month of a given date. We need to select the Advanced Formula.
   • Then select the Function from and click the Insert Selected button to use them. Then, add the date field from the object.
   • Clicking the Insert Field button displays all fields from the campaign object. Here, we need to add an End Date field to display the last date of the month.

DATE (
    YEAR ( EndDate),
    MONTH ( EndDate ) + 1, 1 ) - 1

Let’s understand the above formula to return the last date of the month:

• Here, using the YEAR() and MONTH() functions, we are extracting the year and month of the given date.
• Then, in the extracted month, we add one more month. For example, if the extracted month is August (08), we add one month, resulting in September (09).
• Then we set the 1st date for the new month. As per our example, 1st September.
• Then we will subtract 1 Day from the 1st date of the new month. Here, 1st September – 1 Day = Last Day of previous month.

• Provide this formula and click the Check Syntax button to confirm the formula is correct, and click the Next button.

5. Select the Field Level Security for the formula field, and select the profiles to make the field visible for the profiles. The field will be visible for the profiles selected from the Visible column.
   • After selecting the profiles, click on the Next button.

6. In this step, we must select the field’s visibility on the object’s page layouts. In this case, we have created the formula field for the reports so we can choose not to display it on the page layouts.
   • Uncheck the checkboxes of the page layouts to prevent the field from displaying on them. After this, click on the Save button.

7. We have now created the formula field that will return the last date of the End Date field from the campaign object.
   • As you navigate to the object, you will see the formula field that you created to calculate and display the last date from the campaign end date.

In this way, we can create a formula field to store the extracted last date of the given date month in Salesforce.

Conclsuion

I hope you have an idea about how to get the last day of the month using a Salesforce formula with the DATE() function, so that it can be used in formulas, reports, or automation in Salesforce. In this, I have created and explained a formula using functions that returns the last day of the month.

You may like to read:

• Extract Year from Date Salesforce Formula
• Date Formula Functions in Salesforce
• Add Days to Date in Salesforce Formula

Bijay Kumar

I am Bijay Kumar, the founder of SalesforceFAQs.com. Having over 10 years of experience working in salesforce technologies for clients across the world (Canada, Australia, United States, United Kingdom, New Zealand, etc.). I am a certified salesforce administrator and expert with experience in developing salesforce applications and projects. My goal is to make it easy for people to learn and use salesforce technologies by providing simple and easy-to-understand solutions. Check out the complete profile on About us.