Conditional Formatting, Cells containing dates. How do I have dates and text?

Hi Everyone,

I have a query which I'm sure isn't too difficult but I very much an excel beginner.

As you can see in the picture, I have list of people and each person has an action against them for me to carry out, all the actions have different dates however. At present I am manually colouring them so if my action has to be carried out this week I have it purple, if it has to be carried out next week I have it blue and if it's anytime after that I have it green.

I've starting using the Conditional Formatting rules for date occurring and set it so it automatically changes. As you can see in the picture, when I put in those dates such as '30/08/2013' it changes automatically and that's great. However as you can see below the 3 colours, I don't just want to put in a date and it changes, I want to put in a date and text such as '30/08/2013 - process form' and as soon as I write anything but the date, the colour disappears. Is there anyway that the formatting will still colour the cell with both the Date and Text ??

Any help would be very much appreciated as this is racking my brain!



excel.jpg

Hi Fotis,

Thank you for that, it certainly has got rid of the text issue!

However for some reason instead of colouring in the cell which the text is in, it colours in the cell above it. (as you can see in the picture). Also as this would colour all cells beyond Todays date, how would I make it so that it colours just the cells with this weeks dates in? and then another one for the cells the next week?


Thanks again,

Jonty

excel 2.jpg

Of course, it's attached now. Do you see how the cells that are greater than today's date have green cells above them, rather than being green.
I also want to make it so if the date written down is 2 weeks or more from todays date is another colour.

Cheers.


Test.xlsx