Hide #INVALID DATA

Jonny

Hello,

I am attempting to calculate the difference between two dates. When one of the dates is missing, the cell where the difference would be shown instead contains "#INVALID DATA".

Can anyone please help? I've attached a screenshot. The "Actual Duration" column is the difference between the "Start Date" and "End Date". I would like to have the cell in "Actual Duration" remain blank when the "End Date" cell is blank.

Thanks,

Jonny

David Hersher

Hi Jonny,

Does this work?

=IF(ISBLANK([End Date]1), " ", [End Date]1 - [Start Date]1)

Thanks,

Dave

 

 

Mike Wilday

You can also do this: 

=IFERROR([End Date]1 - [Start Date]1, "") 

And if you want to make that formula more independent use @row instead of a physical reference. 

=IFERROR([End Date]@row - [Start Date]@row, "") 

For more on these formulas see: 

• https://help.smartsheet.com/function/iferror
• https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell#row

 

David Hersher

Hi Mike,

Thanks for sharing the iferror function.  I had never seen it before.  That's the cool thing about the community, almost every time I share how I do something I learn that there's a better way!

Dave

Mike Wilday

You're welcome. It is fun to learn from others! I've done my fair share of learning these past couple of years.  I'm glad I can be an inspiration! 

 

Jonny, did either of our solutions work for you?