Hide #INVALID DATA

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

Find more posts tagged with

Healthcare

Project Management

Comments

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:

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?

Sign in to join the conversation!

Quick Links

Trending discussions

Project Comment/ Update
Hi, Is there a feature in Smartsheet that allows us to add comments daily, weekly, or monthly, and filter them later to review what happened on the project during a specific week or month? or create report ? Thanks JCG
Advise on Workgroups and access for Groups
I have Workspaces that have sheets containing sensitive information. The Workspaces have Individual access to a few people. With in the Workspace, I created Dashboards, with Dynamic Views. Each is set with specific Groups to access specific information. The users in the groups can not see or access the Dashboards, unless I…
Document filtered by form responses
I've created a Smartsheets form. When someone submits the form, I want them to automatically be emailed a document that has resources and links, but the document is filtered to only show the content that corresponds with what they selected in the form. Is something like this possible? What types of documents or automations…