We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Date 'over value' conditional formatting [closed]

asked 2017-03-05 00:17:18 +0200

bizzy gravatar image

updated 2020-09-24 16:02:35 +0200

Alex Kemp gravatar image

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: https://ask.libreoffice.org/en/questi... (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?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-09-24 16:02:42.965115


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.

bizzy gravatar imagebizzy ( 2017-03-07 23:33:30 +0200 )edit

3 Answers

Sort by » oldest newest most voted

answered 2017-03-05 11:42:32 +0200

Lupp gravatar image

updated 2017-03-07 20:30:10 +0200

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 @m.a.riosv did a very similar thing already.

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

edit flag offensive delete link more


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

m.a.riosv gravatar imagem.a.riosv ( 2017-03-05 14:00:33 +0200 )edit

answered 2017-03-05 03:17:03 +0200

m.a.riosv gravatar image

updated 2017-03-08 00:50:30 +0200

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'.


image description

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.



edit flag offensive delete link more

answered 2017-03-07 01:06:20 +0200

bizzy gravatar image

updated 2017-03-07 18:55:01 +0200

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: Test.ods

edit flag offensive delete link more


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.

Lupp gravatar imageLupp ( 2017-03-07 15:50:44 +0200 )edit

Question Tools

1 follower


Asked: 2017-03-05 00:17:18 +0200

Seen: 1,848 times

Last updated: Mar 08 '17