Current within 5 years

Question

QUESTION: I wish to enter a formula to return me if a given date is within 5 years of the current date. If it is over the cell should read "OVERDUE", if the given date is with 5years of the current date then the cell should read "CURRENT".

Thanking you in anticipation

excel learner

ANSWER: Faruk/excel learner,

assume the date is in B2, then in C2

=IF(B2="","",IF(B2>=DATE(YEAR(TODAY())-5,MONTH(TODAY()),DAY(TODAY())),"CURRENT","OVERDUE"))

You can see in the Date Formula, using Year(today())-5 we subtract 5 years from today's date for comparison with the date being tested.

---------- FOLLOW-UP ----------

QUESTION:

Answer

Faruk,

You could use conditional formatting keying on the value returned by this formula.

in Excel 2003 and earlier, conditional formatting is found under the format menu

in Excel 2007 it is found on the home tab in the styles section, the left most button in that section.

I don't know which cell or cells you want to turn a color or where thye are located so let me refer you to Debra Dalgleish's site who has an overview and many pages on conditional formatting

this takes you to who table of contents page and you can look under C and see the contidional formatting topics:

http://www.contextures.com/tiptech.html

or you can go to the conditional formatting basics page directly:

http://www.contextures.com/xlCondFormat01.html

If you need more specific help, post back with version ofand details