Help in Excel

[photogal5367]

Hi all,

I am working on a spreadsheet that I need a formula to do a few things in. 

I have a date - 20101216 in column F4 and I need it to turn red if that date is 5 years or more less than today's date.  I need that date to turn yellow if that date is within 6 months greater than today. And I need it to stay white if the entry in G4 is stating "Initiated"

The current formula I have for the first part is: =AND(G4<>"Initiated",F4>TODAY()-1825)    BUT it's not working right ...can anyone help?

[strollin]

Hi all,

I am working on a spreadsheet that I need a formula to do a few things in. 

I have a date - 20101216 in column F4 and I need it to turn red if that date is 5 years or more less than today's date.  I need that date to turn yellow if that date is within 6 months greater than today. And I need it to stay white if the entry in G4 is stating "Initiated"

The current formula I have for the first part is: =AND(G4<>"Initiated",F4>TODAY()-1825)    BUT it's not working right ...can anyone help?

If you mean that you need to know if the date is 5 years or OLDER than today's date then your formula should be: =AND(G4<>"Initiated",F4<=(TODAY()-1825))

[photogal5367]

Thank you, but what about the second part?  I had it working right yesterday and then I messed with it to try to incorporate the second function of if the date in F4 WILL BE 5 years old within 6 months from today's date, it turns yellow? 

I don't think I worded that correctly the first time.  It's basically a due date of every 5 years, if it's past that date then it's overdue unless the action has been initiated, and I wanted the yellow to act as a notification of what items to initiate action on 6 months in advance.

[strollin]

=AND(G4<>"Initiated",AND(F4<(TODAY()-1825)),F4<=(TODAY()-1645)) tests if the date is between 4.5 and 5 years older than today.