cell to be colored according to expiration date

Question

Hello I would like to highlight different colors for cells containing expiration dates based on certain criteria GREEN: the product is consumable and has not reached the expiration date YELLOW: the product is consumable but the expiration date will be reached in 2 months ORANGE: the expiration date has been reached RED: the expiration date of the product is exceeded by 2 months  and/or  GREEN: the expiration date of the product is not exceeded ORANGE: the expiration date of the product is less than 2 months RED: the expiration date of the product is more than 2 months  and/or  GREEN: the expiration date of the product is not exceeded ORANGE: the expiration date of the product is less than or equal to 2 months RED: the expiration date of the product is more than 2 months   Can you help me by providing details on how to construct the formulas... I am always eager to learn but I wasn't born with IT knowledge... thank you very much for your valuable help

PapyLuc51 · Answer

Hello,  One detail is missing; is it on OpenOffice or LibreOffice?  Is it possible to have a copy of the file (without confidential data) using cjoint - please copy the provided link and paste it in the response.  Best regards

Mike-31 · Answer

Good evening, Yes, Libre Office and Open Office have the same operating criteria and are compatible; however, Conditional Formatting is different from Microsoft Office. I would recommend starting by creating three styles: Red, Yellow or Orange, and Green. The simplest and quickest way is to color a blank cell, for example G5, in red and Format/Styles/New Style give it a easily identifiable name later, for example Red then recolor the cell in yellow and do Format/Styles/New Style give it a easily identifiable name later, for example Yellow and the same for Green delete the colored cell that is no longer needed. Then select the range of reference dates, for example E2:E50 Format/Conditional Formatting and select Manage in the assistant click on Add then select The formula is paste this formula F2>=TODAY()+60 Apply the style select Red Add then select The formula is paste this formula AND(F2TODAY()) Apply the style select Yellow Add then select The formula is paste this formula AND(F2<>"";F2<=TODAY()) Apply the style select Green https://www.cjoint.com/c/HIwsMOOL3Aj See you later Mike-31 I am responsible for what I say, not for what you understand...

Mike-31 · Answer

Re the thread,  For enthusiasts of strict spreadsheet rules, it is quite possible to bypass numerical difficulties regardless of spreadsheets and seniority more difficult for value ranges but very easy for fixed data example on the attached file and in a single condition on Open Office or Libre Office, just create the styles as explained in my previous post and create a small database with the reference value in one cell and the style name in another, for example in M2:N10 where you can add 4 styles between M7 and N10  select the range to be processed, for example K5:K25 and apply this conditional formula STYLE(VLOOKUP(K5;$Sheet1.$M$2:$N$10;2;0)) in Apply the default selected style and that's it https://www.cjoint.com/c/HIxfTYnqp0j  -- A+ Mike-31  I am responsible for what I say, not for what you understand...

Mike-31 · Answer

The forum is a treasure trove of knowledge and exchange, a pleasure to encounter different insights, too bad that sometimes both requesters and contributors forget certain elementary rules.  I have also written a tip that may interest you for bypassing the limit on conditional nesting, but it has not been tested on Open Office here  See you Mike-31  I am responsible for what I say, not for what you understand...

Cell to be colored according to expiration date

4 answers

Full screen mode

Cannot use my ctp18 tablet with meetingbar

Training of the receiving coil

Very small left scrollbar

Electronic invoicing

Anomalies in my laptop usage: virus?

Help: assign a number to a word in another cell

Aol password loss and change of pc

Doctolib plans to use your health data for its ai research

Missing lock icon