I have a question on using two functions with an if statement in Google Sheets as one complete function. Both variables have to be true, otherwise it returns false. I need one function to check the date 20 months back from today. If said cell is less than today's date 20 months back it's true, naturally. However, for the complete function to return true it also searches for another text value in another cell and has to be an exact match. Both conditions have to be true (the date and the exact match) for the function to be true. So if the date in the cell is less than today's date 20 months back and the text value in the other cell is an exact match, function is true.

Problem is that it seems like the date function does not seem to apply.

=IF(D2<DATE(YEAR(TODAY()),MONTH(TODAY())-20,DAY(TODAY())),AND(REGEXMATCH(M2,"text")),TRUE,FALSE)

CodePudding user response：

You current formula is not set up correctly (nor logically). Given only what you've shown here, this should work:

=IF(AND( D2<DATE(YEAR(TODAY()),MONTH(TODAY())-20,DAY(TODAY())), REGEXMATCH(M2,"text") ),TRUE,FALSE)

Notice that the AND( ) contains both conditions here, whereas your original formula had it only around the second condition.

However, a shorter version of this would be as follows:

=AND( D2<DATE(YEAR(TODAY()),MONTH(TODAY())-20,DAY(TODAY())), REGEXMATCH(M2,"text") )

... since the result of a properly functioning AND( ) is always TRUE or FALSE anyway.

CodePudding user response：

It looks like you're supplying 4 arguments to the IF statement:

=IF(DATECHECK,AND(TEXTCHECK),TRUE,FALSE)

The IF statement expects 3 arguments instead. 1) the condition, 2) the value if true, and 3) the value if false. You can combine your two conditions using an AND statement like this:

AND(DATECHECK,TEXTCHECK)

The final formula would then be:

=IF(AND(D2<DATE(YEAR(TODAY()),MONTH(TODAY())-20,DAY(TODAY())), REGEXMATCH(M2,"text")),TRUE,FALSE)