Date 'over value' conditional formatting

Hi, I have a column with dates (UK formatting DD/MM/YY), and would like to set up the cells to change style (eg different colour) once a set number of days has passed from today’s date (I am trying for 2 weeks, or 14 days).

I’ve tried different ways within Conditional Formatting but nothing seems to work. I tried the solutions suggested here without success: Conditional Formatting Based On Date
(No change is made after entering the formulas)

There does not seem to be any other solutions on Google either.
Does anyone have any suggestions to get this working please?

So I finally managed to get it to work. I have no idea what was wrong with the original file but once I copy/pasted the whole spreadsheet into a new file the formulas worked as expected. (The date formatting was correct from the start, I did not change anything apart from copy/paste into a new file.)

Both the solutions by @Lupp and @riosv work as described.

Thank you for the help.

May it be that your dates are actually pieces of text? Ask one of the respective cells =ISTEXT(Reference). Or use ‘Value Highlighting’ Ctrl+F8.

It is essential that the dates actually are recognised as dates by the spreadsheet. This means they are numeric in their internal representation and only specifically formatted as “locale-readable” dates for display.
If you entered / imported / got converted dates as texts, you have to apply the function DATEVALUE() in advance of any arithmetic applied to them or any comparison with dates in default representation.

DATEVALUE is locale dependent and may reject or interpret wrongly text-dates in a different style of writing. The only text format for dates specified to be recognised locale-independent is the ISO 8601 extended format like 2017-03-05 for today (5 March 2017).

The Test.ods file was next to empty. Therefor I still cannot tell anything ´concerning reasons for the failure the OQ complained about.
I just can attach a little demo again. But @mariosv did a very similar thing already.

@bizzy: PLease look for the type of your dates and answer my respective question.

Sure you are right Lupp, I forgot this too much typical issue.

A sample file with a second example with conditions between dates.
For some periods there are some conditions included with CF selecting ‘Date is’ as condition, like ‘Last week’ or ‘Last month’.


Edited 20170308

Attached two minimal sample files, the have the source on the sample file with the problem, the other form scracht.
On the bad direct format has been eliminated.
I’m not able to find what the difference is, but they have different size.
I no one find the difference I can report the bug.



The solution from riosv works and I can replicate this, but for some reason in the actual file that I need to implement this I cannot get this to work even though I think I am copying all the formatting and formulas correctly.

Would you be able to have a look to see if there is an obvious problem within the spreadsheet? I have had to remove most of the data as they were sensitive but the date range in question starts from C522.
Any help is very much appreciated.

EDIT: Thank you for the karma, here’s the file:

Sorry. I wouldn’t subscribe to any service just to get a file here.
@bizzy: I will see for enough karma for you to be able to upload the file by editing your OQ.