Training Planner with different dates calculate the number of days left from latest dates

Hi All

I am running a training planner on excel for circa 350 staff, this is one element of the training that has a 2yr re-occurence, I'm using DATEDIF & TODAY() which gives me overdue dates -281 &
Using =Cell -TODAY() for dates in the future and a 3-way conditional formatting to give me a visual representation of when staff are approaching their due dates Green 730-90 days before training is due Amber for 90-0 days & Red if over due -1 to -730 days.

My first wish is to be able to get Column E to work with Columns B & D to give me the figure in Column E, at present I am manual updating this to change the target from Column B to D when staff take refresher training. Going forward there will be extra columns every two years.

Second wish I am having to change the formula between DATEDIF & TODAY() & Cell-TODAY() for each member of staff depending on whether or not they are in date or overdue, as excel flags up #NUM! if the dates don't match.

I'm not a strong user of Excel if someone can offer some advice on a more eloquent way of doing this I would be a very happy chappy I have attached a jpeg as the table below is not formatted correctly to give everyone the idea of what I'm doing...

Thank You In Advance

Pete

1.jpg

Attaching a sample workbook enables others to work on your problem:

To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and click "manage attachments" to open the upload window.


To add a file to a post

C3,copied down:
=EDATE(B3,24)

E3 copied down:
=MAX(C3,EDATE(D3,24)) or

=IF(D3="","",EDATE(D3,24)) or
=IF(D3="",C3,EDATE(D3,24))

depending on your preferences. more to follow...

Try this for the CF

You're welcome. Thanks for the rep...