How to consider two dates in Google Sheets, in separate columns, and when a 2nd date is detected at-CodePudding

I have a date in column A, for example: 1/2/2022. When 30 days have passed, I use the following formula to tell me something has expired:

=AND($A2<=(TODAY()-30),$A2<>"")

The result if expired: TRUE.

Sometimes, I have another value in column B. This value indicates when something has been closed and expiration should no longer apply.

How can I modify the above formula to take my column B date into consideration? so that if any value is detected in column B at all, my result will be custom text, like "N/A"?

CodePudding user response：

You can use IF or IFS combined with ISDATE or ISBLANK:

=IFS(ISDATE(B2),"N/A",AND($A2<=(TODAY()-30),$A2<>""),"Yes",TRUE,"No")

(put "N/A" if B2 is a date, "Yes" if your original formula evaluates to TRUE, otherwise "No")

=IFS(AND(ISBLANK(B2),$A2<=(TODAY()-30),$A2<>""),"Yes",ISBLANK(B2),"No",TRUE,"N/A")