0

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?

Thanks in advance!

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

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. – Hope Earley Sep 12 at 14:15
  • I tested this formula. Are u getting invalid formula error? Did you rename column with your names? – harshal gite Sep 12 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? – Hope Earley Sep 12 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. – harshal gite Sep 12 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. – Hope Earley Sep 12 at 14:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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