Let me preface this by saying I have inherited a matrix to track employee training levels, and the form factor is not going to be adjusted. This has been manually updated when training becomes due. I'm trying to automate this to a point of having cells change color depending on the date for the highest level that has been acheived. If, for instance L3 has been achieved and training is due, I would like the first 3 levels to turn from Green to Yellow. I've tried a few conditional formats that seem to almost do the job, but not quite. I would like to streamline this, as it is used for several production facilities and hundreds of employees on a number of trainings, I don't want a lot of bulky formulas.

Matrix.png

Here is what I've tried to tie the L1 to the L4 cell.

=IF(ISBLANK(L4),DATEDIF(L3,TODAY(),"m")>=11,DATEDIF(L4,TODAY(),"m")>=11 Format fill color to yellow

It works fine until I start adding all the conditions (i.e., <=10(Green), >=11(Yellow), >=13(NoFill). These are all separate formulas, so by the time I get the L1 cell set up there are at least 9 conditions.

Any ideas on how I can get this to function correctly?