Help on Conditional Formatting Formula

Based on the screenshot, I am trying to color my cells to red if the “# of Days” either are or exceed the targeted number (120 days). For the Pilsner “Style”, there are no colored red cells, and I have no idea why…

I have Pilsner = 175, 154 and 12 days ago.

Need help and thanks!

Hey @Bryan_Wang, welcome to the Community!

First, X days ago is actually a negative number, so -154. So your formula should read

Style = Pilsner
AND [# of Days] <= -120

But generally, are you creating multiple rules for different types of beer that have different shelf life? A much easier approach would be this:

  1. Add a column Shelf Life on your Styles table. Fill out shelf lives for each style — e.g. 150 for Witbier, 120 for Pilsner etc.

  2. Create a single conditional format that will read:

    thisRow.[# of Days].AbsoluteValue() >= thisRow.Style.[Shelf Life]
    

i.e. dynamically compare the number of days to the shelf life of current row’s selected style.

3 Likes

Thank you so much for your help, @Paul_Danyliuk!