Calculated column formula for an expiry date based on drop down selection

Question

I have a dropdown list named “Expiry Period” with options as follow:-

1. Yearly
2. Monthly
3. 2 weeks
4. 1 week
5. Other options might be added later on

Now I want to create a calculated column named “Expiry Date” which will calculate the expiry date based on the (modified date + the “Expiry Period” selection from the above list). For example if the user edit the page on 1 June 2019 , and the user select “Yearly” from the “Expiry Period” , then the “Expiry date” calculated column will be (1/June/2019 + 365 = 1/June/2020), while if the “Expiry Period” = Monthly then the “Expiry date” calculated column will be (1/June/2019 + 30 = 1/July/2019),and so on . But I am not sure if I can define such a formula inside calculated columns?

Answer 1

Keeping in mind the value of calculated columns update only when the Item is Create or Modified or the formula itself changed. Now to your problem, following is the formulate you can use:

if([SingleChoice]="Year",[Created]+365, if([SingleChoice]="Month", [Created]+30, Today()))

Note I am using Created date to generate expiry date. But you can use other columns or formula as well. Expand the function to include other choices, like week, half year etc.

Refer to the this page to learn more on formulas for calculated column in SP.

Answer 2

Yes, You can define formula for those in the Calculated Column. Please put this in the Calculated Field Formula field.

Note: I've tested this in the "SharePoint Online 2019"

=IF((DropDown="Yearly"),TEXT(Modified+365,"MM/DD/YYYY"),IF((DropDown="Monthly"),TEXT(Modified+30,"MM/DD/YYYY"),IF((DropDown="2 Weeks"),TEXT(Modified+14,"MM/DD/YYYY"),IF((DropDown="1 Week"),TEXT(Modified+7,"MM/DD/YYYY"),""))))