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

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?

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.

• thanks a lot for your help. now the formula work well for me , except that if the dropdown choice is not selected, i want to show empty string inside the calculated column, instead of `Today`. i tried to define this `if([SingleChoice]="Year",[Created]+365, if([SingleChoice]="Month", [Created]+30, ""))` but i got `Invalid formula`.. so can i achieve this? Jun 17, 2019 at 20:46
• Hi, I dont see any issue with the formula. Are you referring to right columns in formula? Make sure to use right column internal name, `SingleChoice` is available in my list. Which might be reason. If not, can you paste the exact formula are using. Jun 17, 2019 at 21:05
``````=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"),""))))