Tricky one-Conditional Formatting from todays date

[leanne2011]

Hi

I have pulled together a sheet where depending on todays date I need cells to turn red with certain validation. There are 3 rules that I need to create but haven't a clue how to
Can someone help or can they suggest how I can go what I am asking - even if another way ? Any help would be v much appreciated. I attach the file

The rules are listed in the attachment and I have put lines around the boxes I would expect to turn red following the applied validation.
Leanne

[Paul]

Hi Leanne,

For condition 1, select H2:H9 and use the CF formula:

=AND(H2<>"",H2<=TODAY()+5,H2>=TODAY())

This assumes you don't want cells with dates prior to today to turn red (only dates between today and 5 days ahead). If you want to highlight prior dates as well, remove the last argument ",H2>=TODAY()" from the function.

For conditions 2 and 3, please clarify. You use the term "and or". In Excel, AND and OR are two distinctly different logical operators. If you want to check to see if a cell in column E is blank AND the corresponding cell in column D is less than 5 days out, you could use the following CF formula (after selecting E2:E9):

=AND(E2="",D2<=TODAY()+5,D2>=TODAY())

Same note as above regarding dates prior to today. If you want to use OR, such that the cell in column E turns red if that cell is blank OR the date in column D of that row is within the next 5 days, use:

=OR(E2="",AND(D2<=TODAY()+5,D2>=TODAY()))

Hopefully this gets you along and you can figure out the formula for condition 3?

[leanne2011]

Many thanks - I really appreciate your help on this, you have given me something to manipulate and work for my needs so thanks again.
Leanne

[Cutter]

@ leanne2011

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. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. 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.