Rows coloring acording to product expiry date

[hariskar]

I created a simple spreadsheet with products and their expiration dates. I want 3 colorings green for products that expire in today()+15days, yellow for products that expire in the next 15 days and red for expired products. I want the whole row to be colored.

No problem with expired products: $D2< TODAY() and
products that not expire in the next 15 days: $D2 > TODAY()+15

I have 2 problems:

1. Also empty rows are marked as expired:red. What should I add in the formula to not mark red empty rows?
2. I did not manage to create the middle category: products that have not expired, but will expire in the next 15 days.

Thank you!!

[mariosv]

A formula like =($D2>0)*($D2< TODAY() should solve the issue about empty cells.

A formula like =($D2>=TODAY())*($D2<=(TODAY()+15)) the issue about the middle category

[schiavinatto]

@hariskar, See image:

---

ATTENTION: If you would like to give more details to your question, use edit in question or add a comment below. Thank you.

If the answer met your need, please click on the ball to the left of the answer, to finish the question.

[gregors15]

Hi,
Have a look at the attached sheet, I have used conditional formatting to produce the colours you want, also uploading a screen shot of the conditional formatting. Let us know if it helps.LOQ_20190817.ods(/upfiles/15659961479791023.ods)
LOQ20190817CD.odt You will need to create 3 styles, ExpireYellow for condition1, ExpireRed for condition2 and ExpireGreen for condition4.

[gregors15]

If you have a look at the sheet I uploaded, column C shows difference in days between today and the expiry date, negative means expired, positive yet to expire,

[hariskar]

I saw your file, that way it works for me, but I would like the whole row to be colored. So when I choose the whole table and not only the expire column it does not work as you can see in my attached file in my next post. Obviously there is a conflict between the whole table formulas and the Expired (days) column only formula.

[mariosv]

Please edit your question, don’t use answers.

[hariskar]

OK, sorry.