Search code examples
regexif-statementgoogle-sheetsgoogle-sheets-formulatextjoin

Google Sheets, IF, AND, REGEXMATCH, DATE combined Formula

I am trying to achieve the following:

IF
    Cells B5:B6 and B8:B9 contains ☑
AND
    Todays date is less than 7 days after the date defined in cell B2
THEN
    In the cell the formula is added write 'On Time' otherwise write 'Behind'

I have tried the following formula however it returns a 'Formula parse error'

=IF(AND((REGEXMATCH((B5:B6,B8:B9), "☑")),(B2+7) < today() ), 'On Time', 'Running Behind')

Any ideas on how to achieve this functionality?

Edit

From testing, it seems the issue lies with REGEXMATCH not being able to check 2 different cells in the way I've to define it.

May need to use ArrayFormula(-- from some research

Solution

  • try it like this:

    =IF((REGEXMATCH(JOIN("", B5:B6, B8:B9), "☑"))*
 ((B2+7) < TODAY() ), "On Time", "Running Behind")

    0