Counting for a Blank Cell

Lisi

Hello!

I have this formula that works great:

=DATE(YEAR([Completion Date]3), (MONTH([Completion Date]3) + 3), (DAY([Completion Date]3)))

But if my date in "[Completion Date]3" is blank it throws off my whole formula. What can I add so it stays blank if I have no date in "[Completion Date]3".

 

THANK YOU!

Andrée Starå

Hi Lisi,

You need to add an IFERROR function.

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

Let me know if you have any questions!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Mike Wilday

To be more specific you can wrap the entire formula in the IFerror function like this... 

=IFERROR(DATE(YEAR([Completion Date]3), (MONTH([Completion Date]3) + 3), (DAY([Completion Date]3))),"Insert Date")

This will give you the value of "Insert Date" if there is no date in the Completion Date field. If you want it to be blank, you can Delete the text Insert Date and leave the quotation marks. 

Lisi

Hi Mike,

 

Thank you very much that works perfectly!

Mike Wilday

You're welcome. Glad I could be of assistance. 

Paul Newcome

You could also use your current formula in an IF statement to say if the completion date is an actual date, then run your formula, otherwise leave blank.

 

=IF(ISDATE([Completion Date]3), your formula goes here, "")

Mike Wilday

So true Paul! That's a good idea too. 

Lisi

This is perfect, I was able to answer another question with this also!

 

THANK YOU PAUL!

Paul Newcome

I use this process a lot to build nested IF statements. Sure you could use an AND statement if you have multiple requirements, but a lot of the time I haven't realized the need for it until I have already built the bulk of my formula. In those cases, I'll just wrap it in one more IF function. This also provides the ability to account for things other than errors.