Excel date formula help

barneysplash

Hi all

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 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

aoh

Something like

=IF(DATEDIF(MAX(B2:E2),TODAY(),"m")<11,"OK",IF(DATEDIF(MAX(B2:E2),TODAY(),"m")=11,"Due","To Be Done"))

in the Status cell should get you going

barneysplash

Hi all

I've tried the above formula and it works great. I got another formula
from another forum, but your formula gives an error message if a date in the future is entered.

I put both formulas side by side and did some testing, see "PERSON 6" in the attached spreadsheet.

If a date in the future is entered, your formula gives the error "#NUM" but the other site's formula stays at "OK"

Because the training sheet will be used by several people with varying Excel skills, this error notification is important.

The other site provided some help on using conditional formatting to highlight answers as red, green or yellow. I didn't know
how to do this. Here is their advice, where F2 is the cell that contains your answers i.e. "OK", "TO BE DONE", "DUE".

On the home tab in Excel select:

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

Thank you for your assistance, you are very kind.

Fysh

The error you describe isn't an error so much as an unstated additional requirement It's not clear from your original statement that the spreadsheet would also have future dates entered which are presumably bookings, and this can change the required logic.

Here's a variation on the formula aoh gave you which allows for future dates:

=IF(MAX(B2:E2) > Today(),"Booked",IF(DATEDIF(MAX(B2:E2),TODAY(),"m")<11,"OK",IF(DATEDIF(MAX(B2:E2),TODAY(),"m")=11,"Due","Expired")))

Possible values are "Booked" (future date), "OK" (less than 11 months ago), "Due" (will expire within the month), and "Expired" (more than 12 months ago).

You can use conditional formatting to highlight the cells and make it easier to see which ones are pending.

You can also use the CountIF function to count the status cells which need attention:

=COUNTIF(F2:F7,"Expired")

barneysplash

Hi all

I should have said that there won't be any future dates entered for something like "BOOKED" etc.
The issue is just that some users could input a future date as a typing mistake.

Thanks for that though.

One other request is, I have a training session that is done every three years, can I amend the formula from:

=IF(DATEDIF(MAX(O2:T2),TODAY(),"m")<11,"OK",IF(DATEDIF(MAX(O2:T2),TODAY(),"m")=11,"DUE","TO BE DONE"))

to

=IF(DATEDIF(MAX(O2:T2),TODAY(),"m")<35,"OK",IF(DATEDIF(MAX(O2:T2),TODAY(),"m")=35,"DUE","TO BE DONE"))

and still have it work?

I will test it out and revert with results.

EDIT: I've tested it out with "35" instead of "11" in the above formula, and it works fine

Thanks

Barney