Conditional Calculated Date Column

Question

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

Answer 1

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

2.Please use the below Formula:

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

3.Here is my test:

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

Please follow the steps:

1.Create a Choice column named [Certificates]

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

3.Modify the calculation formula of [Retention Date]

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

4.Here is my test: