# Conditional Calculated Date Column

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

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

1.Create the below columns in the list

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

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

3.Here is my test:

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:

• 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 Dec 15, 2021 at 14:06
• Please see my updated POST 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! 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? 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 Dec 21, 2021 at 14:18