1

I currently have a calculated column [Retention Date] that is only based on one value, the completion date.

This is for certain certificates so I can use them for styling in my DataTable. The issue with this is it only accounts for a 1-year timespan. Different Certificates have different intervals on which they need to be completed it can either be every other year, annually, every 2, or three years.

Is there a way I can incorporate IF[Certificate] = [Certificate 1], add 2 years, and so on? There are about 15-20 different ones, I just cannot figure out the correct syntax for the IF condition in the calculated column.

=DATE(YEAR([Completion Date])+1,MONTH([Completion Date]),DAY([Completion Date]))

UPDATE:

To be more descriptive, there are 19 certificates total. Let's name them Cert 1-Cert 19. Cert 1 is only required once. Cert 2-15 are required annually, so every year. Cert 16, Cert 17, and Cert 18 are required every other year, so every two years. Cert 19 is required every three years. Can I do something like the following to apply a condition?

=IF([Certificate] = "Cert2", IF([Certificate] = "Cert3", IF([Certificate] = "Cert4", IF([Certificate] = "Cert5", IF([Certificate] = "Cert6", IF([Certificate] = "Cert7", IF([Certificate] = "Cert8", IF([Certificate] = "Cert9", IF([Certificate] = "Cert10"....{insert rest of 11-15})))))))))DATE(YEAR([Completion Date])+1,MONTH([Completion Date]),DAY([Completion Date])))

Then I will apply the add one year to all of those, but I am unsure of how to go about every two years, and every three years, and the one that is only required once. Can you have more than one condition in a calculated column?

UPDATE 12/21 So I tried your syntax, and it works but I need to account for all the proper counts. With 0 Interval, there are 3 certificates, with 1 interval, there are 17 certificates, with 2 interval, there is 1 certificate, and with 3 interval there is also 1 certificate.

I tried modifying the condition to fit those in, and that is where the syntax gets wonky.

=IF(Certificate="Cert1",Certificate="Cert2",Certificate="Cert3"),"0",IF(OR(Certificate="Cert4",Certificate="Cert5",Certificate="Cert6",Certificate="Cert7",Certificate="Cert8",Certificate="Cert9",Certificate="Cert10",Certificate="Cert11",Certificate="Cert12",Certificate="Cert13",Certificate="Cert14",Certificate="Cert15",Certificate="Cert16",Certificate="Cert17",Certificate="Cert18",Certificate="Cert19",Certificate="Cert20"),"1",IF(OR(Certificate="Cert21"),"2",IF(Certificate="Cert22","3","")))))
Improve this question

1 Answer 1

Reset to default
2
+50

According to my understanding of your question, you want to calculate the retention date based on different time intervals.

Please follow the steps:

1.Create the below columns in the list

  • [Completion Date]: Date and time
  • [Certificate Interval]: Choice
  • [Retention Date]: Calculated

enter image description here

2.Please use the below Formula:

=DATE((YEAR([Completion Date])+[Certificate Interval]),MONTH([Completion Date]),DAY([Completion Date]))

enter image description here

3.Here is my test:

enter image description here

=========================== Updated Answer =========================

Please follow the steps:

1.Create a Choice column named [Certificates]

enter image description here

2.Create a Calculated column named [Interval], and use the below Formula

=IF(Certificates="Cert1","0",IF(OR(Certificates="Cert2",Certificates="Cert3",Certificates="Cert4",Certificates="Cert5",Certificates="Cert6",Certificates="Cert7",Certificates="Cert8",Certificates="Cert9",Certificates="Cert10",Certificates="Cert11",Certificates="Cert12",Certificates="Cert13",Certificates="Cert14",Certificates="Cert15"),"1",IF(OR(Certificates="Cert16",Certificates="Cert17",Certificates="Cert18"),"2",IF(Certificates="Cert19","3",""))))

enter image description here

3.Modify the calculation formula of [Retention Date]

=DATE((YEAR([Completion Date])+[Interval]),MONTH([Completion Date]),DAY([Completion Date]))

enter image description here

4.Here is my test:

enter image description here

Improve this answer
6
  • Ok, so I should have went into a bit more detail but this is sort of what I am referring to. The Certificate interval is in a separate list that I use to pull through a REST call to populate to a landing page in another table. I was planning on just hard coding the number of years depending on the value of the Certificate name in the condition
    – BeerusDev
    Dec 15, 2021 at 14:06
  • Please see my updated POST
    – BeerusDev
    Dec 15, 2021 at 16:55
  • Hi BeerusDev. I have updated answer under initial My Answer. Hope this can help you to solve issue. Have a nice day!
    – EchoDu_MSFT
    Dec 20, 2021 at 2:38
  • Hey EchoDu! Thanks for updating your answer, unfortunately I receive The formula contains a syntax error or is not supported. I update my post with the exact formula that I entered in the calculated column. Side Note: Shouldn't the column name be wrapped in brackets?
    – BeerusDev
    Dec 21, 2021 at 13:53
  • Also, when I click on the technical details in the error message --> (Learn more about the syntax for formulas.) It is a dead link
    – BeerusDev
    Dec 21, 2021 at 14:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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