How do I show a blank/no value if none of my logical expression is true?

When a date is present in a field, I'm calculating 4 weeks ahead of that date, else I want it to be blank. This is returning an #incorrectargumentset. Thanks!

=IFERROR(IF(WEEKDAY([End Date]16) = 1, ([End Date]16) + 30, IF(WEEKDAY([End Date]16) = 2, ([End Date]16) + 29, IF(WEEKDAY([End Date]16) = 3, ([End Date]16) + 28, IF(WEEKDAY([End Date]16) = 4, ([End Date]16) + 34, IF(WEEKDAY([End Date]16) = 5, ([End Date]16) + 33, IF(WEEKDAY([End Date]16) = 6, ([End Date]16) + 32, IF(WEEKDAY([End Date]16) = 7, ([End Date]16) + 31, ""))))))))

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    This is the most simple way to do it:

    =IFERROR(IF(ISDATE([End Date]16), ([End Date]16) + 28, ""), "")

    In English: If this formula produces an error, leave the cell blank. Otherwise; If the End Date in row 16 is a date value, add 28 days to that date (4 weeks) and put that date in this cell; otherwise, leave this cell blank.

    Is there a reason to use the specific row # in the cell reference? If this formula is on the same row as the End Date, just use [End Date]@row , then it works for every row without any changes.

    But if you're set on adding certain number of days based on the day of the week value, try this:

    =IFERROR(IF(ISDATE([End Date]16), IF(WEEKDAY([End Date]16) = 1, ([End Date]16) + 30, IF(WEEKDAY([End Date]16) = 2, ([End Date]16) + 29, IF(WEEKDAY([End Date]16) = 3, ([End Date]16) + 28, IF(WEEKDAY([End Date]16) = 4, ([End Date]16) + 34, IF(WEEKDAY([End Date]16) = 5, ([End Date]16) + 33, IF(WEEKDAY([End Date]16) = 6, ([End Date]16) + 32, IF(WEEKDAY([End Date]16) = 7, ([End Date]16) + 31, "")))))))), "")

    Just make sure your parentheses' color-coding is good. Your first parentheses in the formula and the last should be the same color! (I entered the formula above into my test sheet and it worked straight away!)


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    This is the most simple way to do it:

    =IFERROR(IF(ISDATE([End Date]16), ([End Date]16) + 28, ""), "")

    In English: If this formula produces an error, leave the cell blank. Otherwise; If the End Date in row 16 is a date value, add 28 days to that date (4 weeks) and put that date in this cell; otherwise, leave this cell blank.

    Is there a reason to use the specific row # in the cell reference? If this formula is on the same row as the End Date, just use [End Date]@row , then it works for every row without any changes.

    But if you're set on adding certain number of days based on the day of the week value, try this:

    =IFERROR(IF(ISDATE([End Date]16), IF(WEEKDAY([End Date]16) = 1, ([End Date]16) + 30, IF(WEEKDAY([End Date]16) = 2, ([End Date]16) + 29, IF(WEEKDAY([End Date]16) = 3, ([End Date]16) + 28, IF(WEEKDAY([End Date]16) = 4, ([End Date]16) + 34, IF(WEEKDAY([End Date]16) = 5, ([End Date]16) + 33, IF(WEEKDAY([End Date]16) = 6, ([End Date]16) + 32, IF(WEEKDAY([End Date]16) = 7, ([End Date]16) + 31, "")))))))), "")

    Just make sure your parentheses' color-coding is good. Your first parentheses in the formula and the last should be the same color! (I entered the formula above into my test sheet and it worked straight away!)


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Kerri S
    Kerri S ✭✭

    It's working perfectly now. I have to reference a prior row to get the correct dates. Great big thank you for your amazing formula skills.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!