Need Help with formula showing Expiring Dates And targets that are met

Hello Everyone,
I hope you can help me. I been wracking my brain trying to get this working but have hit a brick wall.

My problem is as follows. (I will try to keep it simple)

I have a date in Cell A1 (Target Date) where i need to recieve 2 documents
Cell A2 is for the date i recieve Document 1
Cell A3 is for the date i recieve Document 2

I want cell A1 to highlight yellow if it is due to expire in less than 5 days
I want cell A1 to highlight red if the date has expired
Now the issue i have is getting Cell A1 to remain "Unformatted" if the dates in A2 & A3 are less than the target date (Cell A1)

At the moment when the date expires it goes red even if the target is met in Cell A2 & A3.

PLEASE HELP!!!!!

Condition 1 = Cell Value Is Between =TODAY()+1 And =TODAY()+5 This will highlight the cell Yellow
Condition 2 = Cell Value Is Less Than or Equal To =TODAY() This will highlight the cell Red
Condition 3 = Formula Is =IF(A2<A1,A3<A1) this will remain unformatted

I am using the Conditional formatting function in Excel 2003

This does not work, The cell A1 now highlights red if empty

Also, I have tested the formula and the following occured:
The date in A1 = 15/01/13
The date in A2 = 14/01/13
The date in A3 = 14/01/13

Cell A is highlighted red even tho the criteria in A2 & A3 have been met

Ok, this now fixes the issue of the blank cell being highlighted in red but does not solve the issue with the criteria being met. (Explained below)

Cell A1 = 15/01/13
Cell A2 = 16/01/13
Cell A3 = 16/01/13
Using the above criteria, Cell A1 remains unformatted. where it should be RED as the Criteria in A2 & A3 has failed the target date in A1
If i change the date in Cell A1 to the 18/01/13 the cell remains unformatted where is should have gone Yellow to highlight the date is within 5 days of target
If I change the date in cell A1 to the 15/01/13 (Yesterday) the cell goes RED which is correct as the target date has been missed.

Hope this testing helps? Would it help if i uploaded my testing sheet?

Thanks for that, However, Cell A1 is still yellow even tho the criteria has been met in A2 & A3. If A2 & A3 are less than A1 the cell needs to remain unformatted.
using your formula, if you was to change the date of cell A1 to a date in the past, then Cell A2 & A3 to a date prior to this, would A1 go Red? or would it remain unformated as required?

Sorry to be a pain. I really appreciate all our help.

Thanks for this, It has resolved a couple of the issues but there is still 2 errors i have found.

If the date in A1 has expired and the dates in A2 & A3 have not met the target date, it currently remains unformatted
Also, If Cell A1 is in the future and the dates in A2 & A3 are in the past, the cell highlights Yellow when it should be unformatted as the dates in A2 & A3 have met thier target.

The cell does correctly highlight red if the date has expired and A2 & A3 have not both been entered.
and it does correctly highlight yellow if as above, A2 & A3 have not been entered and is witin 5 days.

Hope this helps?

I have carried out a few tests so you can see the outcome.
Hope this helps
formula examples.jpg

Been experimenting with the formulas but still unable to get this to work.
If I could just get it to work with one criteria instead of 2 that would be a help but I cnt get that to work either.
I hope someone can help.

Hiya,

The actual Spreadsheet I am using is quite extensive so have ripped out just the parts i am struggling with (See attached)

Column C is my Target Date. I want this to use a simple traffic light system to stay unformated if more than 30 days away, Yellow if less than 30 days away and RED if date is past. However, if my survey is recieved (Column M) before the target date I would like it to remain unformatted,

Column E is autopopulated with a date 14 days (equal to 10 working days) after Column D. I would like Column E to follow the traqffic light system as above but if the documents in Column F & G are recieved before the date expires in column E, I would like Column E to remain unformatted.

Sorry if I have confused anyone. I was trying to keep things simple.
eVTC Tracker (Used for Testing).xls

Im a little confused what you mean?
Column C (My target date) is dependant on Column M arriving before the target date.

I have actually asked two questions in my previous post
Question 1:
Column C is my Target Date. I want this to use a traffic light system More than 30 days = Unformated, If less than 30 days = Yellow, And RED if date is past.
However, if my survey is recieved (Column M) before the target date (Column C) I would like it to remain unformatted,

Question 2:
Column E is autopopulated with a date 14 days (equal to 10 working days) after Column D. I would like Column E to follow the same traffic light system as above but if the documents in Column F & G are recieved before the date in Column E then I would like Column E to remain unformatted.

Does this help clear things up?

I do use the Today() function. I have also hidden the =TODAY() in Cell A1 in case i need to use it.
The main problem i have is when my target date is met. the Target date still goes yellow & red.

As requested, I have uploaded a new copy of the workbook showing different scenarios all using the same formula.
To keep things really simple. I am only asking for a resolution to Question 1 (Detailed in my previous post)

I have written my findings in a comments field. Please check the forumla i have used in column C and please play around with it if it helps fix my problem.

Once again, many thanks for all your help and comments.
eVTC Tracker (Used for Testing).xls

Amazing job!! Many Many Thanks to everyone's helps and Dredwolf for finding the solution!!