Excel date automatically updates training status

Hi from Ireland

I am looking for some help with and Excel topic.

I have a list of staff with dates they attended annual training.

Some have done the training several times over the last number of years.
Some have attended the training only once, and some have never attended.

Attached is a mock-up worksheet of the kind of thing I'm after.

What I would like to be able to do is to have their status
change automatically from "OK" to "DUE" to "TO BE DONE"

Excel would "look" in the columns date1, date2, date3, date4
and select the latest date and present the status based on
this date.

Excel would "check" the dates to see if they are within the last year
and then show the status as:

OK - done within last year
Due - Done within last year but will expire in the next 30 days
To be done - has not been done within the last year and needs to be done.

Is there any way to do this?

Many Thanks,

Barney

Hi,

Please upload the workbook to this forum. Many of us prefer not to visit file hosting sites of unknown provenance.

With B2 containing 01/01/2011

in F2
=IF(AND(EDATE(MAX(B5:E5),12)<=TODAY()+30,EDATE(MAX(B5:E5),12)>=TODAY()),"DUE",IF(MAX(B5:E5)>TODAY()-365,"OK","TO BE DONE"))
and copy down

Select F2

Conditional Formatting
New Rule
Use a formula to determine...

=(F2="OK")
format as green

=(F2="TO BE DONE")
format as red

=(F2="DUE")
format as yellow

Use Format painter (paintbrush icon) to copy to other cells