IF(ISDATES and Blanks)

=IF(ISDATE([A]@row), IF([A]@row < TODAY(), "Expired", "Valid"), IF(ISDATE([B]@row), IF([B]@row < TODAY(), "Expired", "Valid"), IF(ISDATE([C]@row, "Valid", IF(ISDATE([D]@row, IF([D]@row < TODAY(), "LATE", "Out for Quote")))))))

I have the following statement above. I'm trying to have it say "Late" if Column C has no date and Column D is in the past.

Answers

  • J Tech
    J Tech ✭✭✭✭✭

    Hi @SmartsheetRookie

    It looks like there is a syntax error in the formula you provided. The part IF(ISDATE([C]@row, "Valid", should be IF(ISDATE([C]@row), "Valid",.

    Here's the corrected formula that includes the condition you mentioned:

    =IF(ISDATE([A]@row), IF([A]@row < TODAY(), "Expired", "Valid"), IF(ISDATE([B]@row), IF([B]@row < TODAY(), "Expired", "Valid"), IF(AND(NOT(ISDATE([C]@row)), ISDATE([D]@row), [D]@row < TODAY()), "Late", IF(ISDATE([C]@row), "Valid", IF(ISDATE([D]@row), IF([D]@row < TODAY(), "Late", "Out for Quote"))))))

    This formula checks if column C doesn't have a date and column D has a date that's in the past, and if so, it returns "Late". Otherwise, it continues with the original logic to determine if the row is "Valid", "Expired", or "Out for Quote".

    Regards

    J Tech

    If my response has helped you in any way or provided a solution to your problem, please consider supporting the community by marking it as Insightful, Vote Up, or Awesome. Additionally, you can mark it as the accepted answer, which will make it easier for others to find a solution or get help with a similar issue in the future. Your support is greatly appreciated!