Format cell to turn red after 30 days unless...

Hi there, Very first post from a new excel user. Wonder if you can help. Please go easy on me.

I have created my own invoicing system in excel and the only thing i am struggling to do is track late payments.
I have set up a conditional format [ =NOW()-30 ] which highlights a cell red if the date is over 30 days old (I'd like to set up a traffic light system of yellow 25days, orange 30, red 35days perhaps but thats a bit beyond me right now)
However id like to, in the next column add the date the invoice has been paid and for the colour to change to green perhaps. Is this in anyway possible. I'm a children's counsellor and business is not my thing and I am a very visual person so this may help my brain process my accounts.

Thank you in anticipation

Will Taylor

Hi Will,

The column you'll be adding the invoice paid date; do you want the previously formatted red cell to now go green regardless of the date (so if paid but after 30 days still green)?

If so you can apply conditional formatting in an order as to apply what is required

Could you advise which columns your detail is in or upload a sample file and I'll get this set up for you.

Regards

Dave

Hi Dave.
Yes, I'd like the red cell to change to green. I assumed it would be an additional conditional format, but what it was I had no idea. I have tried to copy the cell down but even without a date in the cell the cell goes red, so I assume that in order to ensure that the cell stays blank rather than red if there is no date in the cell I need to add another conditional format at the top of the list that says - if blank then stay white?

Thank you for your generous support

Will

thats amazing. it does exactly what i wanted it to do. i only wish i understood what you did and why it worked! Regardless, thank you so much for giving your time to this, I may come back at some point and ask again if i get stuck.

My final question is this. Do i have to copy each line of code or is there a way of copying the cell and pasting it to the new document with the conditional formatting attached? Im working on a mac running excel 2011

If you click the box left of column A and above Row 1 to select all cells and then view conditional formatting>manage rules you should be able to see what I've done and replicate that into the document as you require. The reason the formatting works as it does is because they are placed in a certain order.

Let me know if you get stuck

Hi Dave,

I cannot see the attachment, and believe this is what I want to do?
I have invoices due dates set. This is red yellow and green.
red being over due
yellow being due (month before) and green being 30 days + away
Some of the invoices have now been paid so are technically no longer overdue. I have a column next to the date saying "paid", and I can either type paid or yes.
I then want the date (currently in red, as saying overdue) to turn blank? or another colour as its the date is overdue from todays date, BUT the invoice is paid so no longer overdue.
I have tried and tried but cannot seem to do this! Your help would be greatly appreciated. Thanks