Search code examples
google-sheetsgoogle-sheets-formulaspreadsheet

IF formula with specific text string and date

I'm trying to place an IF condition on a cell.

Cell A1 may be any of the following options: 1.Req no (ie. REQ123) 2. A date 3. Any text that does not have the string REQ

Cell A2 will have the formula. What I'd like to happen may be any of the ff:

  1. If cell A1 has the string REQ, show Red
  2. If cell A1 has a date, show Blue
  3. If cell AI has a text (that does not have the string REQ), show Yellow

Also, the formula for the conditions above will be nested on an AND and OR function.

I'm kinda stuck so any help will be appreciated! Thank you!

Solution

  • In this formula, anything that does not match one of the three conditions you listed will show "FALSE". So if you have a number in A1, that will show "FALSE" unless you change the formatting.

    =IF(
  ISDATE(A1),
  "Blue",
  IF(
    ISTEXT(A1),
    IF(
      REGEXMATCH(A1, ".?REQ.?"),
      "Red",
      "Yellow"
    )
  )
)