ISBLANK, IF, AND combination

JamieN
JamieN
edited 01/06/22 in Formulas and Functions

Hello,

I am trying to get the below formula to work however am not having much luck.

=IF(ISBLANK([Customer Owner]9, 0, IF(AND([Start Date]9 < TODAY(), [End Date]9 > TODAY()), 1)))

I would like a check box ticked if the [Customer Owner] is NOT blank and today is between the [Start Date] and [End Date].

TIA

Best Answers

  • JamieN
    JamieN
    Answer ✓

    Got it!!

    =IF((ISBLANK([Customer Owner]11)), 0, IF(AND([Start Date]11 < TODAY(), [End Date]11 > TODAY()), 1))

  • Brent Wilson
    Brent Wilson ✭✭✭✭✭
    Answer ✓

    So using plain language: IF Customer Owner is NOT BLANK AND Start Date is Less Then Today AND End Date is Greater Then Today then Check

    So... IF(AND(NOT(BLANK(Expression1)), Expression 2, Expression 3), True, False)

    Try This

    =IF(AND(NOT(ISBLANK([Customer Owner]@row)), [Start Date]@row < TODAY(), [End Date]@row > TODAY()), 1, 0)

    Be careful about your <... you may want them as <=

    In the expression, I have written if it was to start on today's date it would not show checked..

    =IF(AND(NOT(ISBLANK([Customer Owner]@row)), [Start Date]@row <= TODAY(), [End Date]@row >= TODAY()), 1, 0)

    Would include today's date as within the start and end date rage

    Brent C. Wilson, P.Eng, PMP, Prince2

    Facilityy Professional Services Inc.

    http://www.facilityy.com

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!