Some cells wont highlight with colour with conditional formatting

[nutz104]

test.xlsx
I have seen similar threads on this subject and have tried to follow the advice but they do not seem to work for me.

Basically I have a spreadsheet that tracks dates and the formatting should be: when date is entered - green, date more than 7 but less than 14 days - amber, date over 14 days - red.

I have attached (hopefully) a sample of what I am working with.

Any help will be greatly appreciated; I have been staring at it for the past 2 hours and I think I may be losing my grip on reality

[Fluff13]

Hi & welcome to the board
Try for red
=AND(A1<>"",TODAY()-A1>14)
Amber
=AND(A1<>"",TODAY()-A1>7)
Green
=A1<>""

Sort the rule so that red is 1st, amber 2nd & green 3rd

[nutz104]

Hi Fluff13,

Thanks for getting back to me.

Annoyingly this seems to work for my example sheet but when I try on my main sheet I run into the same problem where it works perfectly for some and others are completely unaffected.

I'm attaching a straight copy (formatting and all) from of the column I'm having issues with. I have tested this with the suggestions you gace Fluff but still no luck.

Am I doomed?

Attachment 636348

[Fluff13]

I can't view the attachment, it comes back as invalid.

to attach a workbook
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

However if it doesn't work on all cells I suspect that some are text & not dates.
Select the column in question & change the format to General all the dates should then look like 43670
Any that still look like 24/07/2019 are text

[nutz104]

ok second times the charm with the attachment.

in the meantime i will look into what you said about the date format.

thanks again for your help.

[nutz104]

Ok so just tried the date/general formatting and they do indeed change, so they are formatted as dates.

[Fluff13]

You need to delete all the rules, then select A1 to the end and add the rules I posted in post#2
You have simply added them from Row43 downwards

[nutz104]

THANK YOU FLUFF!!!!!!!!!!!!!

After a little tweeking to work with the live sheet I finally have it working.

Thank you for your help Fluff, greatly appreciated.

[Fluff13]

You're welcome & thanks for the feedback