I'm working on a "user safety training record," sheet. I have multiple other training assessment sheets that when completed, index/match to an employee number row and complete the training column/field [Orientation], [Trng 2], [etc]. If an employee has completed all the training successfully, they get "Green" symbol in that column's training cell, or if unsuccessfully, they get a "Red" symbol. It also index/ matches the date of the training as it expires yearly. What I want is an evaluation of all of the various completions and a "Green" or "Red" dot if they have failed an assessment or if a passed assessment has exceeded 365 days.
My formula is:
=IF(OR([Orient Pass/Fail Assess]@row = "Red", [Trng 2 Pass/Fail Assess]@row = "Red", [Trng 3 Pass Fail Assess]@row = "Red"), "Red", IF(OR([Orient. Pass/Fail Date]@row < TODAY() - 365, [Trng 2 Pass/Fail Date]@row < TODAY() - 365, [Trng 3 Pass/Fail Date]@row < TODAY() - 365), "Red", "Green"))
The problem is if a cell is blank, it is considering that cell as really old, thus returning "Red" as it has exceeded the 365 day time limit. I have considered ISBLANK, but can't seem to make it work. Thoughts?
