Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
ShowAll Questionssorted byDate Posted
sonyas

# Expiary month / year needed from formula

Hi

I am trying to create a formula from the following fields:

Created Date
Term (Pick list > 1yr, 2yr, 3yr)

To give me the month & year that the policy will expire

Can anyone help?

Thank you

Sonya
Buell
Give this a shot:

Code:
```DATE(
YEAR (CreatedDate) + (FLOOR((TODAY() - CreatedDate) / 365) +
IF(ISPICKVAL(TERM,'1yr'),1,
IF(ISPICKVAL(TERM,'2yr'),2,3))),
MONTH(CreatedDate),
IF(AND(DAY(CreatedDate) = 29,MONTH(CreatedDate ) = 02) , 28, DAY(CreatedDate)))```

You will need to replace CreatedDate with the name of your created date field, same with term, and 1yr,2yr with the appropriate picklist text.

Buell
Give this a shot:

Code:
```DATE(
YEAR (CreatedDate) + (FLOOR((TODAY() - CreatedDate) / 365) +
IF(ISPICKVAL(TERM,'1yr'),1,
IF(ISPICKVAL(TERM,'2yr'),2,3))),
MONTH(CreatedDate),
IF(AND(DAY(CreatedDate) = 29,MONTH(CreatedDate ) = 02) , 28, DAY(CreatedDate)))```

You will need to replace CreatedDate with the name of your created date field, same with term, and 1yr,2yr with the appropriate picklist text.

This was selected as the best answer
sonyas
Hi Buell

Thanks for that, the formula works in the way it returns a date, but it doesnt seem to matter what year I choose, it always returns a date 3 yrs later, regardless of the year selected in the pick list! this is the formula used....

DATE(
YEAR (Start_Date__c ) + (FLOOR((TODAY() - Start_Date__c ) / 365) +
IF(ISPICKVAL(Term__c ,'1year'),1,
IF(ISPICKVAL(Term__c ,'2year'),2,3))),
MONTH( Start_Date__c ),
IF(AND(DAY( Start_Date__c ) = 29,MONTH( Start_Date__c ) = 02) , 28, DAY( Start_Date__c )))

Regards
Sonya
Buell
It sounds like the issue is with the picklist values.  Are they '1year' like you have in the formula or '1 year' or '1 Year'?
sonyas
Hi, Your correct, i've changed it & it now works perfectly, Thank you so much for your help.