Rows coloring acording to product expiry date

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!!

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

@hariskar, See image:

image description


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 Descrição da imagem to the left of the answer, to finish the question.

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.

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,

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.

Thank you all for kind help! Now I have my 1st working spreadsheet! Sorry I do not have > 5 points to upvote your posts…

Edit:

And another question: I would like to create a column (column E) that shows in how many days (without decimals) a product will expire or has expired.

=DATE(YEAR(D6);MONTH(D6);DAY(D6))-TODAY() for each cell I put it in. But it does not work for negative values eg for the expired products.
I also tried $d2-today() which also does not work with negative values. Both show numbers like 1899-01-18 instead of a negative value.

Here is the link of the file: https://www.mikroviologos.gr/Expired_3.ods

Please edit your question, don’t use answers.

OK, sorry.