[Solved] Conditional Formatting Formula

[KeithOO]

3 date cells in same row - sometimes empty. I require another cell to monitor these 3 cells and, if any contain a date which is
1 More than 30 days' old or
2 More than 60 days' old or
3 More than 90 days' old,
one of 3 styles would be triggered.
Anyone got a suggested formula please?
Thanks.
Keith

[Zizi64]

You can use the STYLE() cell function for this - combined the IF(Condition;True;False) and NOW() Cellfunctions

Or you can use the 'Conditional formatting' feature of the Calc application. From menu:
Format - Conditional formatting...
(This function is different in LibreOffice and Apache OpenOffice)

...Please read the Help first...

[KeithOO]

Thanks. I use Conditional Formatting successfully for something else but I am trying to avoid spending a a lot of trial and error time on this one.
Can you suggest a formula for Conditional Formatting? I can set up the styles OK.
Thanks.

[Zizi64]

You can apply the STYLE function in a Formula. You can not apply it for cells contain pure data (for strings, numbers or dates).
But you can use the "Conditional formatting" feature for pure data too.

See this example:

Style_function.ods

(84.03 KiB) Downloaded 488 times

[KeithOO]

Thanks. Not 100% sure what I'm looking at. Is this completely separate from Conditional Formatting?
In the meantime I've simplified my problem so that I am just focussing on the one date cell in a row, i.e. depending on the date, the date cell changes colour using Conditional Formatting.
I am using (DAYS(TODAY();$C14)>60) with >45 and >30 for the other Conditions 2 and 3.
This seems to work fine except where there is no date and the cell is empty. In this case it calculates it as 42191 days and therefore applies colour to the cell.
Any thoughts why 42191 for empty cells?
I would also like to understand the file you kindly prepared and would appreciate any clarification.
Apologies that my knowledge is not greater.
Thanks.
Keith

[Zizi64]

Any thoughts why 42191 for empty cells?

Because the value of an empty cell equals zero.
The zero value represents the base datetime value.
(Today value) - 0 = Today value ((scaled in number of days))

In other words: The result is the number of days since the base date

(see Help, and in menu: Tools - Options - OpenOffice Calc - Calculate - Date)

[KeithOO]

Thanks. The penny had just dropped when I received your response, as I was thinking that it was probably treating an empty cell as a zero.
Assuming I didn't miss the answer in you response, what is the best way around it?
I had thought that maybe the formula could say If cell is empty, then ignore. Or is there a better way?
Thanks again.

[Zizi64]

I had thought that maybe the formula could say If cell is empty, then ignore.

Yes, it is a right way.
See the ISBLANK() function.

[KeithOO]

CF(Conditional Formatting) seems to request a required positive condition (no chance to say "If this .. do this but If ... etc.") and therefore I found it difficult to use ISBLANK() as I didn't know how to make it into IS NOT BLANK. My CF formula is AND(S36>"";DAYS(TODAY();T36)>60) I inserted >"" for an adjacent cell which just contains text but which is always empty when date cell is empty. I couldn't use >"" on the date cell as it only seemed to work on a text cell. I would prefer to use ISBLANK() if I knew how to insert it into my CF formula. Any suggestions?
Thanks again.

[Zizi64]

For column A:

Code: Select all

REM condition 1.:
AND((TODAY()-A3)>90;A3<>0)

REM condition 2.:
AND((TODAY()-A3)>60;A3<>0)

REM condition 3.:
AND((TODAY()-A3)>30;A3<>0)

For column B:

Code: Select all

REM condition 1.:
AND(NOT(ISBLANK(B3));(TODAY()-B3)>90)

REM condition 2.:
AND(NOT(ISBLANK(B3));(TODAY()-B3)>60)

REM condition 3.:
AND(NOT(ISBLANK(B3));(TODAY()-B3)>30)

Conditional formatting.ods

(10.95 KiB) Downloaded 275 times

[KeithOO]

Perfect. Column B is exactly what I wanted.
Thanks for all of your efforts.
Re: Style_function.ods you created above. I haven't used it but would like to understand it. If there is any reading matter you could point me to, it would be appreciated.
Once again,
Köszönöm (Hope I'm saying Thank You, and not swearing at you !!)

[Zizi64]

Köszönöm

Perfect spelling. (Better than my english.)

[Zizi64]

Re: Style_function.ods you created above. I haven't used it but would like to understand it. If there is any reading matter you could point me to, it would be appreciated.

The function STYLE() is an additive function. You can use at end of a formula: it will determine the style of the cell (containing the formula). It have one input parameter: it is a string, the name of a cellstyle to apply. (See cellstyles by hit F11 or from menu: Format Styles and Formatting)

You can "calculate" (select) that string by function
IF(condition;string1;string2))
it will pass one of two string parameters to the STYLE function. (the passed string = the name of the cellstyle to apply)

The STYLE() function have an output parameter too. It will add 0 value to the result of the original formula. It is not necessary in this case. The function T() (TEXT()) will eliminate the passed output parameter of the STYLE() function, because the 'text value' of 0 is "" (empty string)


The result of the entire "function combination" will be in
B column: a Date-like string.
C column: a decimal fraction number, represents the difference in days.

note:
The difference between the functions NOW() and TODAY() is: the return value of the function NOW() is a float with decimal fraction (integer part represents the years, months and days, and the fraction part represents the hours, minutes, and seconds),
The result of the function TODAY() is an integer, represents years, months and days only: it have not fraction part.