Formula to return status

[acg2636]

I am trying to create a functional spreadsheet for an HR department. There are several background checks that must be renewed each year, some every two years and others every 3 & 5 years.

In cell A1 is the date in which the most recent search was completed. Then I would like the formula to go into the next cell of B1. I need a formula that will return with "Current", "Due", or "Over Due" so that we can create a slicer and make the spreadsheet more manageable overall.

I'm not sure if that makes any sense or not.

 

[etaf]

how does excel know to look up and see if the requirement is

each year, some every two years and others every 3 & 5 years

IF( A1 < Date(Year(today())-5,month(today()),day(today()) ) , "over 5 year" , IF( A1 < Date(Year(today())-3,month(today()),day(today()) ) , "over 3 year" ,IF( A1 < Date(Year(today())-2,month(today()),day(today()) ) , "over 2 year" , IF( A1 < Date(Year(today())-1,month(today()),day(today()) ) , "over 1 year" , "Current" )

 

[acg2636]

This is great. Right now, I don't have it built in to let excel to know what the requirement is.

 

[etaf]

I don't have it built in to let excel to know what the requirement is.

Ok, so the formula I gave will just give the status of the years - to get the status Due or over , it needs to know if 1,2,3,4,5 years before it can flag as over or if Due