Conditional Formatting based on date in cell

[Harley78]

Confused and need help.

I enter todays date in AK8, then I created two C.F. Yellow if 14 days and Pink if 7 days from the AK8 date I enter. this C.F. is for Column AK13-600

the two C.F. are:

Yellow Highlight equals =AND(AK13>TODAY(),AK13<=(TODAY()+14))
Pink Highlight equals =AND(AK13>TODAY(),AK13<=(TODAY()+7))

Now I entered 10/12 (Shown as 12-Oct) in AK99 and it turned Pink?

Wondering what I did incorrectly?

Maybe it was a glitch, I was able to turn it to clear however, dealing with 600 rows, I only like to look at pinks during the week.
Any suggestions?


Thanks

Bill

 

[Joe4]

I am not quite clear on your question, but you seem to mention entering a date in AK8 to be used in the calculation, but then your formulas do not use that date, but rather use TODAY() instead.

 

[Harley78]

Guess I was wondering if my formulas were correct since there are times pink should be yellow. Is there an easier method?

 

[Joe4]

You must have done something wrong, as 10/12 is 18 days away, so would not fall within either of the 7 or 14 days time spans.
So nothing should be highlighted.

Many times people get unexpected results when they try to apply Conditional Formatting to a whole range at once, and do not align their formulas with their range selection.
Try doing it on a single row first to make sure that you have the formula working the way you want.
Then, select the whole range that you want to apply it to, and write the formula as it applies to the very first cell in your selection. If you have written the formula correctly, Excel will correctly apply it to all the other cells in your selection.

Also, I would recommend not overlapping your conditions (i.e., 5 days out would meet both formula you wrote). I would have 0-7 and then 8-14, instead of 0-7 and 0-14 (which can work, if the formulas are placed in the correct order, but it cleaner to have no overlap).

 

[Harley78]

Excellent suggestion. I will do that and will most likely work better and cleaner.

Thanks for the tip!

 

[Joe4]

You are welcome.

Write back here if you run into any issues, and provide your example and formulas.