1

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?

3

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.

  • 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? – SharePoint TestDev Jun 17 at 20:46
  • 1
    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. – ThinkB4Code Jun 17 at 21:05
  • not sure what happened now it is working well. – SharePoint TestDev Jun 17 at 21:24
1

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"),""))))
  • in my case i want the calculated column to Date, so is this possible? – SharePoint TestDev Jun 17 at 20:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.