Conditional formatting comparing multiple dates

CASSIFRASS

Hello!

I am trying to create a conditional formatting rule for my field trip scheduling. I want the dates that are requested to compare to already approved dates. If a date is already in the approved column, the requested dates will turn red to indicate that the date is unavailable. If the date is not in the approved column, the requested cells will turn green. I am a Smartsheet newbie, so I would very much appreciate detailed explanations. TIA!

Jason Albrecht

Hello @CASSIFRASS

Welcome to the Smartsheet community.

I'm wondering if this earlier conversation on 'Return Value if Between Dates' has some helpful suggestions for you to get started?

If you are simply looking to see if two dates match and to then change the formatting of the cell, you will likely need a 'helper' column that does the calculation and returns a true or false statement (you may want to use a symbols or checkbox column, or hide it altogether). You can then use the Conditional Formatting button in the toolbar to look for a true/false statement and apply your desired format to the desired cell(s) or entire row.

The formula in the helper column might look like (where 1=true and 0=false):

=IF([Date Requested]@row=[Date Approved]@row, 1, 0)

Then, clicking the Conditional formatting button in the toolbar (6 boxes with a couple greyed out) and adding a new rule, in the <set condition> / "1. Select column for condition", choose you [Helper] Column and for "2. Select criteria" set to "is equal to... 1." Then choose your format and cells. (NB. there's a little video and extra information here, on Conditional Formatting).

CASSIFRASS

@Jason Albrecht Thanks for your reply! Since I need to reference an entire column, I changed the formula to =IF([Date requested]@row = [Approved date]:[Approved date], 1, 0) but I am getting an invalid error. I have the columns set to date format only, perhaps this is throwing it off?

Jason Albrecht

Hi @CASSIFRASS,

In that case, we'll need to change the formula to an index / match, like this:

=IF(INDEX([Approved date]:[Approved date], MATCH([Date requested]@row, [Approved date]:[Approved date], 1)) = [Date requested]@row, 0, 1)

Be aware that the Approved date column will need to be in ascending order for this formula to work. Otherwise, change the 1 in the MATCH function to -1 if the dates are sorted in descending order, or 0 if the dates are not sorted.