Help with Conditional Formatting on Anniversary Date

M

MsButterfly

I have a spreadsheet for tracking employees hire dates, absenses, etc. I
would like for the column with each person's hire date (Column H) to turn red
a week before their anniversary date to alert me to give them a review. The
column next to it (Column I) is where I will fill in the date that the review
was done.

How can I format these two column so that 1) the cells in the date of hire
column turn red a week before their anniversary date, and 2) so that when the
reviewed date is entered the cells changes back? Please help!! This may get
me a raise!!!
 
B

Bob Phillips

Use a formula of

=AND(DATE(YEAR(TODAY()),MONTH(H2),DAY(H2))-7<=TODAY(),DATE(YEAR(TODAY()),MONTH(H2),DAY(H2))>=TODAY())
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

1st, 5th, 10th, 15th Anniversary dates for employees base on Hire 3
Conditional Formatting cells with dates 2
Conditional formatting with dates 7
Conditional formatting with two columns 0
Mail Merge Help 1
Cobditional Formatting Rows based on dates in action plan. 0
Assigning a value to one cell based on the date in another... And then doing it again 0
Vacation Accruals 5

Top