Nested IF in Google Sheets Issue

Question

I have a column of dates, with IF formulas in two other columns. The first IF statement looks for the Max Date in the date list and simply prints TRUE when found.

The second IF statement is meant to print TRUE when the most recent date prior to the Max Date is found.

Originally, I had the following for this:

=IF(B2=WORKDAY(MAX(QQQ!B:B), -1),TRUE,FALSE)

On occasion, however, a day of data will not exist, so the statement must continue beyond Max Date - 1. For this, I tried:

=IF(B2=WORKDAY(MAX(QQQ!B:B), -1),TRUE,IF(B2=WORKDAY(MAX(QQQ!B:B), -2),TRUE,IF(B2=WORKDAY(MAX(QQQ!B:B), -3),TRUE,FALSE)))

The issue with this second approach is TRUE prints for Max Date -2 and Max Date -3, when both exist. I expected that the final condition would be skipped when Max Date - 2 exists, but that's not what occurs.

Any ideas on how this might be better handled are appreciated.

Answer 1

To get the date before the max date, can you use something like:

=query({unique(QQQ!B:B)},"where Col1 is not null order by Col1 desc limit 1 offset 1",0)

Example without offset:

Example with offset: