need blanks instead of #invalid data type

jillian.n.vdm

I am trying to have smartsheet calculate the difference between a start date and end date, but I will never have all dates in the sheet. I need it to return blanks when either start or end date is missing instead of #invalid data type.

Can someone please help? I already tried a conditional formatting rule to white out the #invalid data type text, that doesn't work, and several iterations of the "" I would usually use to return blanks in excel.

Thank you!!!!

Benjamin Walker

Jillian,

 

Try using the IFERROR function:

=IFERROR({formula}, "")

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

 

Ben

Mary Ayers

How about this: =IF([End Date]1 >= [Start Date]1, [End Date]1 - [Start Date]1)

Mike Wilday

Benjamin is accurate. The Iferror function will display a desired result if you are getting an error, which you are.

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

Basically, you are saying if the function I am running has an error, then give me a blank. 

=IFERROR(YOUR FORMULA HERE, "")

Jason Anderson

+1

Once I worked this out I use it in almost all of my formulas now. Great for prompting for missing data.

Wanting to pull the year out from another date cell 

=IFERROR(YEAR([End date]4), "no date")

if there is no date it tells the user why nothing showed up in this cell.