Print this page

How can I determine and display the expiration or end date of a record?

Knowledge Article Number 000004519
Description

How can I determine and display the expiration or end date of a record?

Resolution

Here is a method to determine and display the expiration or end date of a record with a start date and a number of months field. This requires all the following formula fields. The end result is a displayed DATE-type field showing the Expiration.

Assumptions:

- Object has a DATE field called StartDate
- Object has a NUMBER field called Number_of_Months__c
- Month means calendar month, not 30-day month
- Ignores Feb 29

Example: Campaign starting October 21, 2008 and lasting 3 months would end Jan 20, 2009 (Jan 21 would be the beginning of a 4th month).

FORMULA FIELD 1: Hide on page layout

Field Name: Expiration Year

Data Type: Formula (Text)

Formula:

TEXT ( YEAR( StartDate ) + FLOOR( ( MONTH( StartDate ) - 1 + Number_of_Months__c ) / 12 ) )

 

FORMULA FIELD 2: Hide on page layout

Field Name: Expiration Month

Data Type: Formula (Text)

Formula:

LPAD ( TEXT (

IF

( MOD( MONTH( StartDate ) + Number_of_Months__c, 12 ) = 0 , 12 , MOD ( MONTH( StartDate ) + Number_of_Months__c , 12 )

)

), 2, "0" )

 

FORMULA FIELD 3: Hide on page layout

Field Name: Expiration Day

Data Type: Formula (Text)

Formula:

LPAD ( TEXT (

IF ( AND ( Expiration_Month__c = "2" , DAY( StartDate ) > 28 ) , 28 , IF ( AND ( DAY( StartDate ) = 31, OR ( Expiration_Month__c="4", Expiration_Month__c="6", Expiration_Month__c="9", Expiration_Month__c="11" ) ) , 30 , DAY( StartDate )

)

)

), 2, "0" )

 

 

FORMULA FIELD 4: Display on page layout

Field Name: Expiration Date

Data Type: Formula (Date)

Formula:

DATEVALUE( Expiration_Year__c & "-" & Expiration_Month__c & "-" & Expiration_Day__c ) - 1

**************

Alternate method:

Create a single formula field, with the return type 'Date', using the syntax:

DATE (
YEAR ( StartDate__c ) + FLOOR ( (MONTH ( StartDate__c ) -1 + Number_of_Months__c)/12),
CASE ( MOD ( MONTH ( StartDate__c )+Number_of_Months__c, 12 ),0,12,MOD ( MONTH ( StartDate__c )+Number_of_Months__c, 12 )),
MIN ( DAY ( StartDate__c ),
CASE ( MOD ( MONTH ( StartDate__c )+Number_of_Months__c,12 ) ,9,30,4,30,6,30,11,30,2,28,31 ) )
)

 





promote demote