Conditional formatting date range

I am trying to track dates on my spredsheet but am having great difficulty figuring out the formatting for the date ranges. I need:

all dates before today = black fill/white font
today-next 3 months = red fill/yellow font
next 4 months-next 6 months = yellow fill/black font
next 7 months + = no fill/black font

I am using Excel 2007, and I have very little excel experience so please dumb down the answer. Thanks for any help you all can offer!

What cells are used for the dates?

Pete

I currently have all the dates in collumn H.

Okay, assume you have a header row and that your data starts in H2, then highlight all the dates from H2 downwards, then click on Conditional Formatting | New Rule and choose "Use a Formula ...". Put this formula in the box:

=AND(H2>0,H2<TODAY())

then click the Format button and on the Font tab, Color drop-down choose White, then on the Fill tab choose black, then OK your way out.

You can add the other conditions in the same way, i.e. highlight the data, click CF, click New Rule, then Use a formula ..., and you can use these formulae and settings in turn:

=AND(H2>=TODAY(),H2<=TODAY()+91) with a format of Red fill and Yellow foreground, then

=AND(H2>TODAY()+91,H2<=TODAY()+182) with a format of Yellow fill and Black foreground.

There is no need to set a format for the 4th condition that you listed.

Note that I've simplified it to 91 days per quarter.

Hope this helps.

Pete

Thank you Pete for your quick response, however for some reason it isnt working right for me. The dates way in the future, such as Nov 2020 are black fill/white font even though they shouldnt have any formatting since it is more than 6 months in the future. Also past dates such as Jan 2006 have no formatting even though they should be black fill/white font. Please help, I feel like this should be any easy fix that I am missing due to my inexperience. Thanks again for your help!

Perhaps you hadn't highlighted the correct range. Can you attach the workbook, and I'll set it up for you? The FAQ describes how to attach a file to your post.

Pete

blank template..xlsx

Pete,
Im not sure I attached the workbook corrrectly, so if it doesnt work, please let me know. Also I removed all information from the book except the dates so you could see the ranges I work with and test if it works.

I've applied the CF that I recommended earlier to the attached file. You can only see two of them in operation, but if you change the date in cell H31 to 1st June 2013 then you will also see the effect on a date of between 3 and 6 months in the future.

Hope this helps.

Pete

Thank you so much both Pete and FDibbins! I am able to finally start working on my spreadsheet thanks to Pete, and I saved the formatting FDibbins wrote in case I lose the pre written rules Pete input for me. You guys are amesome!