I am attempting to devise a training tracker and have set up the EDATE function to show courses that are expiring at different intervals i.e. 1, 2 and 3 years. I have set up the conditional formatting to show when these courses are 30 days from expiring and everything appears to be working well. However, not all staff members require the same training, therefore i am deleting the EDATE formula within these cells as to not show the serial date which is then being highlighted as expired due to my condtional formatting rule. This is counterproductive as if needs change i am then adding the foruma back and as each staff is different i am not confident that this method will not fall foul to human error.
Is there a method for excel to ignore the corresponding cell EDATE is linked to when there is no data inputted to prevent this from occuring? If this is not possible, is there a different way i should be approaching this?
Current formulas being used: =EDATE(K7,12) or =EDATE(L7,24) or =EDATE(M7,36)
Conditional Formatting being used: =$P7<TODAY()+30 ; i also have a rule in place which does not apply to cells that are blank.
Really would appreciate some direction on this so I can put the matrix in place. I have attached a screenshot of where i have got to.