Condition color format with a date

Hello everyone,

I am no excel expert and I have no idea how to do what I would like to do. I feel I almost made it, but somehow, something is missing which is why I am opening this thread.

So my issue is. I am building some small CRM for sales. And, what I would like is basically a color code for a date that is related with a task. Let me explain :

Example :

I put a task for day 15/09/2014. I would like that the color of that cell would be green anytime before until 09/09/2014 and that it turns orange from 10/09/2014 until 30/09/2014 and after that, that it would turn red.

Do you think it's possible?

I have tried to set color formatting. But the only thing I have managed was under Conditional Formatting > New Rule > Use a formula to determine which cell to format. And then add =P3+15<TODAY() making the color red. However when I applying format painter to other cells, if there is no date, then the cell is red and not normal empty. I even tried to add a second rule to use white but failed. It stays red even if I apply "stop if true"


So even with this last small example I am totally failing on being able to create this cell condition color format with a date if you know what I mean.

Please forgive me in advance if this message was not clear.

Ideally I just want a cell turning into 3 colors according to the date, if you still have time for that task (green), if you should really start working on it (orange), and if you are really late about it (red).

Thank you for your help

Hi, Thanks for the reply and the file.

Either I don't understand either I wasn't clear. what you sent me seems to be fine but for one time use. Here is actually the example of my file. You may see that what I would actually want is on the cell "Next Contact" Database.xlsx

The condition (if date goes over a month) is working on P3. But if there is nothing written, then cell is red. Basically I need that when the person sees the color of that cell according to what I mentioned previously that she/he knows how urgent is the task.

IN the file you sent me all seems to be according to the dates you did put on cell G and H. What I would need is something that goes along with time. I don't know if I am clear. :D Either there is something that I really don't understand or I wasn't clear about what help I needed :D

Please note that I am a real excel beginer. I managed to find today() formula online but have no idea how to work with it :D my level is more =sum() :D

Anyone to help me on this? :/