IF BLANK Formula

Cheryl Collins

Hi

I am trying to input a formula to calculate 3 months from a date in another column but, I don't want a #invalid date error showing in that column when there is no start date. I've tried using the following formula:

=IF(ISBLANK(DATE([Start Date]@row, MONTH([Start Date]@row) + 3, DAY([Start Date]@row)), " "))

But, I get a #Incorrect Argument error. I've tried adding another IF but, it doesn't like it. I know it's going to be something real simple but, I just can't get to the bottom of it!

TIA

Cheryl

Kelly Moore

Hey @Cheryl Collins

The IF ISBLANK is looking for blank cells and if it is true, your formula will execute. You wanted to exclude the blank cells.

The ISDATE is the function you were looking for. Not only will it exclude blank date cells, it will exclude cells if someone entered text.

The formula provided above is also missing the YEAR portion of the DATE function.

Try this.

=IF(ISDATE([Start Date]@row), DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 3, DAY([Start Date]@row)))

We could have also used the IFERROR function - a very handy formula. It says run whatever formula you have but if there's an error do xxxxxx.

=IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 3, DAY([Start Date]@row)), "")

For more info, see below

https://help.smartsheet.com/function/isdate

https://help.smartsheet.com/function/iferror

cheers,

Kelly

Kelly Moore

Hey @Cheryl Collins

The IF ISBLANK is looking for blank cells and if it is true, your formula will execute. You wanted to exclude the blank cells.

The ISDATE is the function you were looking for. Not only will it exclude blank date cells, it will exclude cells if someone entered text.

The formula provided above is also missing the YEAR portion of the DATE function.

Try this.

=IF(ISDATE([Start Date]@row), DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 3, DAY([Start Date]@row)))

We could have also used the IFERROR function - a very handy formula. It says run whatever formula you have but if there's an error do xxxxxx.

=IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 3, DAY([Start Date]@row)), "")

For more info, see below

https://help.smartsheet.com/function/isdate

https://help.smartsheet.com/function/iferror

cheers,

Kelly

Cheryl Collins

Hey @KDM

This has worked a treat! Thank you so much - I'd have never got to that by myself I don't think!

Thanks again

Cheryl

Cheryl Collins

Hi @KDM

I have found a slight issue with the formula you suggested to auto generate dates in as far as it doesn't like it if it staggers a year. e.g. if the Start Date was 02/12/20 (2nd December 2020), I get an INVALID VALUE error?

TIA