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","")))))