Conditional formatting of multiple dates in Excel - with IF functions as well

Hi there,

I have a couple of things I'm trying to figure out how to do...

I'm creating a date tracker, which shows on a month by month basis whether certain date specific events have happened or not.

My column headers are:
A - due date
B - completion date
C - on track?
D - Jan-14
E - Feb-14
F - Mar-14
G - O - etc for all of 2014

I will input the due dates at the start, then as things are completed, I'll enter the completion date.
Then I need to input a formula into column C, and then formulas in columns D - O. All formulas will need to work off conditional formatting and return a coloured cell (either green, orange or red).

Column C formula
Even though I'm entering day specific dates, this column will work off a monthly basis. If the due date is significantly in the future (not due this month or next month) I'd like the cell in column C to be clear. If the due date is this month or next month, it should be orange, and if the due date is last month or later, then it should be red. I thought about using the TODAY()+30 option (for example) but I think that's too general, something due on the 1st of the month will stay orange until the 31st, then be red on the 1st of the next month and stay red for all time - until the completion date is entered. Once the completion date is entered, the cell (regardless of timing) should be green.

Columns D - O formula
Again, this works on a monthly basis and returns similar info but split out further.
If the completion date is in December, only the December cell in the whole row should be coloured, and it would be green.
If the due date is in November and it's currently January - August then the whole row would be clear (unless it had been completed early, in which case it would be green as above).
If the due date is in November and it's currently September, then the September cell would be orange, then when it became October, the September cell would be blank and the October cell would be orange.

Am I trying to achieve something that is too hard? How do I achieve this? And, if I can't achieve, any suggestions of how else I could present this information?

Thanks in advance!

Rosie

Hi,

Welcome to the Forum.

Can you please post the template you have? Use "Go Advanced" option and then the paper clip icon to file upload.

Hi there,

I haven't really made the template yet, because I'm not sure if it's even possible to do what I'm wanting to do, but I've whipped something up that is akin to what I'm hoping to achieve.

Thanks,

Rosie