# OPEN OFFICE CALC - How do I use conditional formatting give me a background based on the distance from a date AND value of a cell?

If I have "Alpha", "Bravo" or "Charlie" in column "A" and "Annual", "Semi-Annual" or a blank cell in column "B", How can I use conditional formatting to change the background color of a cell in the corresponding row with a date in it based on what column "A" and "B" are AND based on how far the date is from the current date?

Example: In "C1" I want the cell background to be green if "ALPHA" is in "A1", "Semi-Annual" is in "B1", and the date entered in "C1" is less than 5 months old. Amber if the same conditions are in "A1" and "B1" but the date is between 6 months and 5 months. Red if it is more than 6 months. But if "Annual" is in "B1" then I want the dates to work pretty much the same way but 11 months to 12 months old. Also if there is a way to turn the cell black if "A1" is anything but "Alpha".

Example2; In "D1" I want the cell background to be green amber or red if "ALPHA" or "BRAVO" is in "A1" and all the other above conditions are the same.

I'm not really sure how else to clarify my question. So if you're trying to help. Thank you. And if you have any questions about my question I will try my best to clear it up.

-
I presume you understand the basics of Conditional Formatting. So use formulae like `=AND(A1="ALPHA", B1="Semi-Annual", (TODAY()-C1) < 5*30)`. If the `5*30` day approach isn’t what you want, look at the `YEAR()`, `MONTH()`, and `DAY()` functions. –  Scott Jun 20 '13 at 1:12