Conditional highlighting based on two variables: a date and the contents of another cell

Hi Rod,

Here's a possible solution.

It requires two changes to your table:

1. An added column to calculate and display the date when a review is due.
2. Replacing the 'codes' with each of the three values expressed as a duration of xDay(s).

I placed the 'due date' column to the right of the column in which the 'days to review' setting is made using pop-up menu cells.

Here's an example:

Column A contains the entry dates. These are samples, and are not ordered by date as I wanted dates that would trigger highlighting and that would not trigger highlighting using rules that compared that date with "Today."

Cells in column B contain a pop-up menu which includes a 'blank' choice and the three duration values shown.

Column C contains the formula shown below the table, entered as shown in cell C2, then filled down to the end of column C.

Highlighting rules for cells in column C are shown in the right sidebar. They highlight the due date with orange when Today is that date, and with red when Today is after the due date.

Removing the highlight can be done be deleting the contents of the cell or by selecting the cell, showing the CH rules, and deleting the two rules for that cell.

Highlight removal could also be done using checkboxes in column D to be checked when each review was done, and revising the formula shown to place a null string ( "" ) in that row of column C when the box was checked

Current formula: IF(ISBLANK($A2),"",$A2+B2)

Revised:    IF(OR(ISBLANK($A2),$D2),"",$A2+B2)

Regards,

Barry

Here my proposal

In column C is the calculation for the number of offset days based in the input in column B

Formula for C2: XLOOKUP(B2,{"1D";"1W";"1M"},{1;7;30},"-",0,1)

XLOOKUP has a list with your inputs {"1D";"1W";"1M"} and a list with the offset days {1;7;30}

In column D is the calculation for the critical date

Formula for cell D2: IFERROR(IF((A2+C2)≥TODAY(),"-",A2),"-")

If you are on / past the critical date the original date will be shown, if you still have time a "-" will be shown.

IFERROR is needed to prevent error message

Conditional Highlighting is done by comparing the 2 dates.

Create this for row 2 and the drag all cells A2 to D2 down, using the yellow dot.

Based on your region the , or the ; will be used to separate the different sections of a formula.

You ca hide the columns that you don't need to see, after you have tested everything.

Hope this will solve your question, please let me know if something in unclear.

Ralf

OK, take your time.

If you like you can include the XLOOKUP function in the Critical Date calculation, that would save you the Offset column

Formula for C2: IFERROR(IF((A2+XLOOKUP(B2,{"1D";"1W";"1M"},{1;7;30},"-",0,1))≥TODAY(),"-",A2),"-")

Ralf

Hi Rod,

You are welcome.

Thank you for the interesting question.

Regards,

Barry