# Calculated Date Column - Multiple IF and more

I've been trying to work this out for 2 days and I'm probably overcomplicating this but here's what I'd like to do.

If the employee has been employed >= 2 years they only need to take certain classes annually, and one class every three years. If employed less than 2 years, one of the courses is annual instead.

Hired is a date column. Courses is a choice column of class options. Last Taken is the date the course was last taken. The calculated column Next Due, showing the next due date after inputting the class and date the employee took it. I did have minor success with IF(OR but not having success with the below. I tried to figure it out in Excel but I'm unable to build the formula there. Today is a column for the current date. I can either use a workflow to update daily or I can delete it after getting my formula right to trick SP (I've done both).

``````=IF(Today>=DATE(YEAR([Hired])+2,MONTH([Hired]),DAY([Hired])),
IF([Courses]="Class1",DATE(YEAR([Last Taken])+1,MONTH([Last Taken]),DAY([Last Taken]))),
IF([Courses]="Class2",DATE(YEAR([Last Taken])+1,MONTH([Last Taken]),DAY([Last Taken]))),
IF([Courses]="Class3",DATE(YEAR([Last Taken])+3,MONTH([Last Taken]),DAY([Last Taken]))))
``````

The part below is if employed less than 2 years. It's just part of the code I lifted out of what I've been working on. I know the syntax is incorrect but no matter what I do (if I join them, use OR, use AND) it's just not working.

``````IF([Courses]="Class1",DATE(YEAR([Last Taken])+1,MONTH([Last Taken]),DAY([Last Taken]))),
IF([Courses]="Class2",DATE(YEAR([Last Taken])+1,MONTH([Last Taken]),DAY([Last Taken]))),
IF([Courses]="Class3",DATE(YEAR([Last Taken])+1,MONTH([Last Taken]),DAY([Last Taken]))))
``````

I don't know how to get it all to work. And I do use Notepad++ and check my brackets but it's fruitless. Can someone help?

Edited to add this new section

``````=IF(Today() >= DATE(YEAR([Hired])+2,MONTH([Hired]),DAY([Hired])),
IF([Courses]="Class1",DATE(YEAR([Last Taken])+1,MONTH([Last Taken]),DAY([Last Taken])),
IF([Courses]="Class2",DATE(YEAR([Last Taken])+1,MONTH([Last Taken]),DAY([Last Taken])),
IF([Courses]="Class3",DATE(YEAR([Last Taken])+3,MONTH([Last Taken]),DAY([Last Taken])),"")))),
//need this section also - how to integrate with above?
IF(Today() <= DATE(YEAR([Hired])+2,MONTH([Hired]),DAY([Hired])),
IF([Courses]="Class1",DATE(YEAR([Last Taken])+1,MONTH([Last Taken]),DAY([Last Taken])),
IF([Courses]="Class2",DATE(YEAR([Last Taken])+1,MONTH([Last Taken]),DAY([Last Taken])),
IF([Courses]="Class3",DATE(YEAR([Last Taken])+1,MONTH([Last Taken]),DAY([Last Taken])),""))))
``````

You had added the conditions in a wrong way. IF(condition, success, fail). Here is the correct way of your formula.

``````=IF(Today() >= DATE(YEAR([Hired])+2,MONTH([Hired]),DAY([Hired])),
IF([Courses]="Class1",DATE(YEAR([Last Taken])+1,MONTH([Last Taken]),DAY([Last Taken])),
IF([Courses]="Class2",DATE(YEAR([Last Taken])+1,MONTH([Last Taken]),DAY([Last Taken])),
IF([Courses]="Class3",DATE(YEAR([Last Taken])+3,MONTH([Last Taken]),DAY([Last Taken])),""))))
``````

Note: I did not change any condition to check your requirement. Just corrected the formatting of your formula.

As I see your updated requirement, only 1 condition is needed to check if the date hired date is older than 2 years and assigning next due as year+3. Rest all have Year+1. SO here is the formula you should use.

``````=IF(AND(TODAY()>=DATE(YEAR([Hired])+2,MONTH([Hired]),DAY([Hired])),[Courses]="Class3"),
DATE(YEAR([Last Taken])+3,MONTH([Last Taken]),DAY([Last Taken])),
DATE(YEAR([Last Taken])+1,MONTH([Last Taken]),DAY([Last Taken])))
``````

Formula is tested so it should not give you invalid formula error.

• Thanks. Unfortunately, I get an error with that also. Commented Sep 12, 2019 at 14:15
• I tested this formula. Are u getting invalid formula error? Did you rename column with your names? Commented Sep 12, 2019 at 14:20
• My fault, so sorry! Yes, that works perfectly, thank you. Any suggestions on how would I add the last part to the code that (else) if hired date is less that two years? Commented Sep 12, 2019 at 14:36
• The first condition in the above formula is today >= date hired+2 right. So that actually means hired date is less than 2 years. Commented Sep 12, 2019 at 14:44
• So I have my greater than and less than confused? OK. Then how would I add in an else that says if employed greater than 2 years? I need both. Commented Sep 12, 2019 at 14:50