Pulling hair Out with Conditional Formatting

[despatcher]

I have been on this for a week and cannot seem to get it correct. i think it is something to do with the order but i am sure i have tried all possibilities. Anyway here goes

Setting the Scene

At work we conduct practical and theoretical performance based assessments depending on how well you do you are awarded a grade which then determines how long you wait before your next assessment:

A = 18 Months
B = 12 Months
C = 6 Months

What i Would Like to do

I have set up an excel spreadsheet to record the exact Run Out Dates (ROD) for the 60+ employees what i want in the conditional format is

Box Colour Clear/Normal for someone who is in date +42 days from TODAY()

Box Colour to turn Amber When ROD is within 42 days(6 Weeks) from TODAY()

Box Colour to turn Red within 14 days (2 Weeks) from TODAY()

I have done the conditional formats for this but cannot quite get it to work either nothing happens or cell will be Amber when it should be red or if someone is not brought back into date on time when their ROD is +1 from TODAYS() date it turns Clear?Normal as the conditons are not met

Any help or another method would be great (I have already considered throwing PC out of window but led to believe i would only have to explain this to the Stores Dept)

I am sure someone out there can see the solution straight away my problem is

I cannot see the woods for all the trees are in the way

Thanks in Advance

 

[shades]

Howdy. Can you post what formulas you put in each of the conditions?
________
CONCEPT X

 

[despatcher]

Condition 1 less than = TODAY()

Condition 2 between = TODAY()+1 = TODAY()+14

Condition 3 less than or equal to =TODAY()+42

I have tried these in varying sequences been looking at it that long confusing myself

 

[shades]

Okay, you don't need any condition for TODAY() + 42

I assume that the data is starting in Cell E2 with dates. The dollar sign on E allows you select as many rows and columns as you want, and they will all change according to the following instructions.

In Condition 1, on the left choose "Formula is", then put this in the box:

=AND($E2>TODAY(),$E2<=TODAY()+14)

And choose your Red format (from button)

Click the ADD button to add Condition 2, choose "Formula is"

=AND($E2>TODAY()+14,$E2<TODAY()+42)

Then choose the Formatting (amber color).

You may have to adjust whether the condition is <= or < (likewise whether >= or >). But make sure that they don't overlap.
________
HONDA NT700 SPECIFICATIONS

 

[despatcher]

On the right track

Shades many thanks for your help you were in the right ball park unlike me this is what i tweaked and it seems to be the solution for the date issues

=AND($H18>=TODAY(),$H18<TODAY()+14)

This turns red within two weeks of $TODAYS DATE$

=AND($H18>TODAY()+15,$H18<TODAY()+43)

This turns Amber greater than two weeks less than six weeks of $TODAYS DATE$

Your help was appreciated

 

[shades]

Great. Glad to help, and thanks for coming back with your solution.

(You might want to check what happens with Day 14. It seems that your formula might have to be adjusted slightly for the #14.)
________
JEEP HURRICANE