Ask Your Question

Rows coloring acording to product expiry date

asked 2019-08-16 19:28:25 +0200

hariskar gravatar image

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

edit retag flag offensive close merge delete

4 Answers

Sort by » oldest newest most voted

answered 2019-08-17 00:57:47 +0200

gregors15 gravatar image

updated 2019-08-17 01:05:18 +0200

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.C:\fakepath\LOQ_20190817.ods(/upfiles/15659961479791023.ods) C:\fakepath\LOQ20190817CD.odt You will need to create 3 styles, ExpireYellow for condition1, ExpireRed for condition2 and ExpireGreen for condition4.

edit flag offensive delete link more


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,

gregors15 gravatar imagegregors15 ( 2019-08-17 10:53:59 +0200 )edit

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.

hariskar gravatar imagehariskar ( 2019-08-18 06:41:15 +0200 )edit

answered 2019-08-17 07:56:57 +0200

hariskar gravatar image

updated 2019-08-17 11:02:50 +0200

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


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:

edit flag offensive delete link more


Please edit your question, don't use answers.

m.a.riosv gravatar imagem.a.riosv ( 2019-08-17 11:01:16 +0200 )edit

OK, sorry.

hariskar gravatar imagehariskar ( 2019-08-17 11:05:06 +0200 )edit

answered 2019-08-17 00:39:45 +0200

@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.

edit flag offensive delete link more

answered 2019-08-16 23:57:34 +0200

m.a.riosv gravatar image

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

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-08-16 19:28:25 +0200

Seen: 40 times

Last updated: Aug 17