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

Kerri S

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, ""))))))))

Jeff Reisman

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!)

Jeff Reisman

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!)

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.