Date 'over value' conditional formatting

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

bizzy
23 ●1 ●2 ●6

updated 2017-03-05 00:20:10 +0100

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 close merge delete

Comments

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 ( 2017-03-07 23:33:30 +0100 )edit

add a comment

3

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

Lupp
10948 ●24 ●81 ●149

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

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

(Editing:)
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

Comments

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

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

add a comment

2

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

m.a.riosv

20148 ●7 ●47 ●123

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

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

ConditionalFormatDatesBetween.ods

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.

TestCFBad.ods

TestCGood.ods

edit flag offensive delete link

add a comment

0

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

bizzy
23 ●1 ●2 ●6

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

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

Comments

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 ( 2017-03-07 15:50:44 +0100 )edit

add a comment

Date 'over value' conditional formatting

Comments

3 Answers

Comments

Comments

Question Tools

Stats

Related questions

Date 'over value' conditional formatting edit

Comments

3 Answers

Comments

Comments

Question Tools

Stats

Related questions

Date 'over value' conditional formatting