Conditional formatting due date
Swipp
-
PapyLuc51 Posted messages 4569 Registration date Status Member Last intervention -
PapyLuc51 Posted messages 4569 Registration date Status Member Last intervention -
Hello everyone!!
In order to regularly update certain files of my staff, I would like to have a table with different colors depending on the validity.
The box will mention the expiration date. I would like it to be red once this date has passed, orange between this date and the 3 months prior, and green if everything is fine.
I've been browsing forums on the subject but haven't been able to achieve my goals.
I am using LibreOffice Calc.
Thank you for your responses, see you soon!!
In order to regularly update certain files of my staff, I would like to have a table with different colors depending on the validity.
The box will mention the expiration date. I would like it to be red once this date has passed, orange between this date and the 3 months prior, and green if everything is fine.
I've been browsing forums on the subject but haven't been able to achieve my goals.
I am using LibreOffice Calc.
Thank you for your responses, see you soon!!
1 answer
-
Hello,
I am not on my computer (under repair), I am providing these directions for the Excel spreadsheet but the formulas are the same
The conditional formatting formulas for a date in A1 (if it's just for the cell itself, there's no need to lock the column; if it's to color the row, use "$" to lock the column as indicated below)
RED
=AND($A1<>"";TODAY()>$A1)
ORANGE
=AND($A1<>"";EDATE($A1;-3)<TODAY())
GREEN
=AND($A1<>"";EDATE($A1;-3)>TODAY())
Replace A1 with the first cell of the target dates column
The conditional formatting should be applied in this order
Best regards
