Conditional Formatting Years

[danidunlop]

Hi there,

I want to create a conditional format for a column that states "change this column to red when today minus the date in column G equals 20 years or greater" and then subsequently another column to change to blue for 30 years, etc.

For example, an employee receives recognition when they have been employed with the company for 20 years, then 30 years, then 40 years and so on. Column A contains information and should change colour when they are eligible to receive their nomination for 20 year recognition, column B would contain information and should change colour when they are eligible to receive their 30 year recognition, etc. Column G is their original date of hire.

Does anyone know how to create this formula?

Much appreciated.

[FDibbins]

Hi, welcome to the forum

You would probably need to use something like...
=A1>=EDATE(TODAY(),240)
the 240 is 12*20...so adjust that as needed.

If you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[danidunlop]

Thank you for responding. I'm still having difficulty. I've attached a sample of my data and the goal is to have the cells turn a different colour when they reach their dates (20 years from date of hire, 30 years from date of hire, etc.).

[JohnTopley]

Try

=TODAY()>=EDATE(E2,360) Blue


=TODAY()>=EDATE(E2,240) Red

Rules must be in above order

[FDibbins]

Thanks for the assist, John

[danidunlop]

Worked perfectly! Thank you VERY much.

[FDibbins]

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.