Loading

Multiple Formulas to Add Months to calculate End Date

Date de publication: Oct 13, 2022
Description

Adding some number of months (term) to a start date to calculate end date may seem straightforward enough, but there are different interpretations and business needs for how many days a "month" should be since actual calendar months are 28, 29, 30, or 31 days long.

There are additional considerations and possible pitfalls such as:

  • Properly calculating the ending year
  • Handling leap years
  • Avoiding non-existent dates such as February 30 and April 31

The following are several formula field solutions to the different ways to add "months" to a Start Date to determine an End Date including these variations:
  1. End Date uses day of month from Start Date restricted to last day of short end months
  2. Month = 28 days (or 30 or 31, or any other fixed number)
  3. End Date is always on a given day of the month (e.g. the 1st, 15th, etc.)
  4. End Date uses day of month from Start Date but pushes out to 1st day of next month if needed to avoid invalid date at end of short months

Each of these formula field examples uses these input fields; change each to your actual field names:
  • Start Date (Start_Date__c): date
  • Months (Months__c): number, 0

To implement any of the formulas here:
  • Substitute in your actual field API names
  • Select "Treat blank fields as blanks" at the bottom of ALL these formula fields
  • If your Term/Months is a picklist field of number values, reference it in these formulas as VALUE( TEXT( Picklist_Field_API_Name ))
  • Option: Implement as process builder field update to a true Date data field to allow End Date to be manually edited after the calculation or to avoid formula field character limit

 

 

Résolution
VARIATION 1: Use day of month from Start Date and restrict to last day of short months

Simply use the ADDMONTHS() function for these requirements.

Create formula field (date): "End Date"

Description: "Add number of calendar months, but restrict to last day of short months as needed"

Help text: "Start Date + Months restricted to last day of short months as needed"

Formula:
IF( ISBLANK( Start_Date__c ) 
|| ISBLANK( Months__c ) , 
NULL , 

ADDMONTHS( Start_Date__c , Months__c )
)


VARIATION 2: Month = 28 days (or 30 or 31, or any other fixed number)
NOTE: To use a number other than 28, substitute in that preferred number in place of 28 in this formula.

Create formula field (date): "End Date"

Help text: "Months are 28-day periods"

Formula: 
IF( ISBLANK( Start_Date__c ) 
|| ISBLANK( Months__c ) , 
NULL , 

/* Add given number of 28-day months to Start Date */
Start_Date__c + ( Months__c * 28 )
)


VARIATION 3: Use a given day of the month (e.g. the 1st, or the 15th, etc.) for all End Dates

NOTE: For End Date to always have a given day of the month, never choose a number greater than 28 since this would lead to invalid dates for the shorter months.

Create formula field (date): "End Date"

Description: "Mathematically determine end year & month, but use fixed company standard day of month for all End Dates."

Help text: "End Date is always on the 15th of its month"

Formula:
IF( ISBLANK( Start_Date__c ) 
|| ISBLANK( Months__c ) , 
NULL , 

/* Calculate End Date */
DATE(

/* Calculate the year of the End Date */ 
YEAR( ADDMONTHS( Start_Date__c , Months__c )) ,

/* Calculate the month number of the End Date */ 
MONTH( ADDMONTHS( Start_Date__c , Months__c )) ,

/* Set end day */
15 )  /* Fixed company standard End Date day of month */    Substitute in preferred day
)


VARIATION 4: Use day of month from Start Date; push invalid dates in short months to 1st day of the next month (e.g. change April 31 to be May 1 instead)

Create underlying hidden formula fields to make ultimate formula field easier to read, create, modify, and understand. NOTE: This variation includes the creation of 3 formula fields.


1) Create hidden formula field (number, 0): "End Year"

Description: "This hidden field will be referenced in End Date formula field."

Formula:
IF( ISBLANK( Start_Date__c )
|| ISBLANK( Months__c ) ,
NULL ,

/* Calculate the year of the End Date */
YEAR( ADDMONTHS( Start_Date__c , Months__c ))
)


2) Create hidden formula field (number, 0): "End Month"

Description: "This hidden field will be referenced in End Date formula field."

Formula:
IF( ISBLANK( Start_Date__c )
|| ISBLANK( Months__c ) ,
NULL ,

/* Calculate the month number of the End Date */
MONTH( ADDMONTHS( Start_Date__c , Months__c ))
)


3) Create formula field (date): "End Date"

Description/Help text: "Start Date + Months on same day of the month unless beyond the end of a short month, then the 1st of the next month"

Formula:
IF( ISBLANK( Start_Date__c )
|| ISBLANK( Months__c ) ,
NULL ,

/* Check for invalid End Date beyond the end of short months */
IF(

/* February invalid End Date for leap and non-leap years */
( End_Month__c = 2
&&
(
( MOD( End_Year__c , 4 ) <> 0  /* Not a leap year */
&& DAY( Start_Date__c ) > 28 )  /* Beyond Feb 28 */
||
( DAY( Start_Date__c ) > 29 )  /* Leap year beyond Feb 29 */
))

|| 
/* 30-day month and End Date would fall on the 31st  */
(( End_Month__c = 4   /* April */
|| End_Month__c = 6   /* June */
|| End_Month__c = 9   /* September */
|| End_Month__c = 11 ) /* November */
&& DAY( Start_Date__c ) = 31 ) ,

/* Use 1st day of next month to avoid invalid End Date at end of shorter months */
DATE ( End_Year__c , End_Month__c + 1 , 1 ) ,

/* Or use same day of month as Start Date if End Date is a valid date */
DATE( End_Year__c , End_Month__c , DAY( Start_Date__c ))
))
Numéro d’article de la base de connaissances

000381089

 
Chargement
Salesforce Help | Article