Ask Your Question

Conditional formatting, two formulas work to colour a row, but not both.

asked 2015-05-19 12:21:55 +0100

stanthesam gravatar image

updated 2015-08-27 10:35:24 +0100

Alex Kemp gravatar image

In Calc, version: and Column B contains a formula which results in the cell containing "Yes" or empty. Column I cells contain a date or empty. In conditional formatting the formula '$B2="" ' makes the empty rows change colour and the formula 'AND(TODAY()-$I2>1900,$I2<>"")' changes the row colour for dates over 5 years. However I can’t get them to work together. Any help would be greatly appreciated.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2015-05-19 14:38:58 +0100

pierre-yves samyn gravatar image


Your formula for calculating dates over 5 years is based on years of 380 days, it is surprising ... You can use:

EDATE(TODAY(); -60).

Otherwise, to answer your question uses only one condition:

AND($B2="";$I2<EDATE(TODAY(); -60))

Replace AND with OR depending on whether you want both conditions are met to apply the format or if one is enough

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2015-05-19 12:21:55 +0100

Seen: 72 times

Last updated: May 19 '15