If, And, Star Help

Options

Hi SS Community!

I wrote a formula that is not intending as I wanted it to.

Looking for some help.

Here is my formula:

=IF(AND(ISBLANK([Entered in WD]@row), TODAY() > [Assigned To OSU]@row + 4), 1, 0

Here are my sheet columns:

I want to STAR if a PEP was assigned to the OSU but not entered in WD after five days of it being assigned.


Where did I go wrong? Right now it is throwing the star on all blanks...

Best Answers

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Answer ✓
    Options

    Use NETDAYS(), NETWORKDAY(), or NETWORKDAYS() to find the number of days between two dates.


    One way to evaluate for 5 days passed would be,

    NETDAYS( [Assigned To OSU]@row , TODAY() )>5

    Your formula would be,

    =IF(AND(ISBLANK([Entered in WD]@row),NETDAYS([Assigned To OSU]@row,TODAY())>5),1,0)

    To exclude non-working days, use NETWORKDAY() or NETWORKDAYS().

    You'll find documentation for these and other Smartsheet functions here: https://help.smartsheet.com/functions

    Hope this helps.

    Cheers!

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    edited 06/07/21 Answer ✓
    Options

    Hi, Tabitha.

    The formula,

    =IF(AND(ISBLANK([Entered in WD]@row),NETDAYS([Assigned To OSU]@row,TODAY())>5),1,0)

    assumes that [Assigned To OSU] will never be empty and that it will always be a date. Otherwise, NETDAYS() will return an error because it'll be unable perform the calculation.

    If there is a chance that [Assigned To OSU] might be empty or contain a non-date value, then we need to include IFERROR() to handle those exceptions. Assuming that you want to return "not true" when [Assigned to OSU] is empty, then the IFERROR() statement will be...

    IFERROR(NETDAYS([Assigned To OSU]@row,TODAY())>5,FALSE)

    and the formula, with exception handling, will be...

    =IF(AND(ISBLANK([Entered in WD]@row), IFERROR(NETDAYS([Assigned To OSU]@row, TODAY()) > 5, FALSE)), 1, 0)


Answers

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Options

    Use NETDAYS(), NETWORKDAY(), or NETWORKDAYS() to find the number of days between two dates.

    Since you're evaluating for 5 or more days passed, the criterion would be

    NETDAYS( [Assigned To OSU]@row, TODAY() )>5

    Your formula would be

    = IF(AND(ISBLANK([Entered in WD]@row), NETDAYS([Assigned To OSU]@row,TODAY())>5),1,0)

    Use NETWORKDAY() or NETWORKDAYS() if you want to exclude non-working days.

    You'll find additional references for Smartsheet functions here: https://help.smartsheet.com/functions

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Answer ✓
    Options

    Use NETDAYS(), NETWORKDAY(), or NETWORKDAYS() to find the number of days between two dates.


    One way to evaluate for 5 days passed would be,

    NETDAYS( [Assigned To OSU]@row , TODAY() )>5

    Your formula would be,

    =IF(AND(ISBLANK([Entered in WD]@row),NETDAYS([Assigned To OSU]@row,TODAY())>5),1,0)

    To exclude non-working days, use NETWORKDAY() or NETWORKDAYS().

    You'll find documentation for these and other Smartsheet functions here: https://help.smartsheet.com/functions

    Hope this helps.

    Cheers!

  • Tabitha W.
    Tabitha W. ✭✭✭✭
    Options

    Oh, I see- thank you so very much!

  • Tabitha W.
    Tabitha W. ✭✭✭✭
    Options

    I just tried the formula and it returns #invalid:



  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    edited 06/07/21 Answer ✓
    Options

    Hi, Tabitha.

    The formula,

    =IF(AND(ISBLANK([Entered in WD]@row),NETDAYS([Assigned To OSU]@row,TODAY())>5),1,0)

    assumes that [Assigned To OSU] will never be empty and that it will always be a date. Otherwise, NETDAYS() will return an error because it'll be unable perform the calculation.

    If there is a chance that [Assigned To OSU] might be empty or contain a non-date value, then we need to include IFERROR() to handle those exceptions. Assuming that you want to return "not true" when [Assigned to OSU] is empty, then the IFERROR() statement will be...

    IFERROR(NETDAYS([Assigned To OSU]@row,TODAY())>5,FALSE)

    and the formula, with exception handling, will be...

    =IF(AND(ISBLANK([Entered in WD]@row), IFERROR(NETDAYS([Assigned To OSU]@row, TODAY()) > 5, FALSE)), 1, 0)


  • Tabitha W.
    Tabitha W. ✭✭✭✭
    Options

    I often forget the "not true" aspect of the formulas.

    This makes total sense and I appreciate the thorough explanation!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!