Netdays formula only on rows with a column value

Hello! I'm looking for some help with the Netdays formula. I am trying to create a helper column that tracks the number of days between a date column titled "Start Date" (when a project began) and a date column titled "Completion" (when a project is completed). My current formula, which is just a start, is this:

=NETDAYS([Start Date]@row, Completion@row)

While it yields a correct value for rows with both dates present, it generates following error for those rows without completion date:

#INVALID DATE TYPE

I believe this error is impacting my sheet summary where I am trying to track the average days it is taking my team to complete tasks. Is there a way to run the Netdays formula on the entire column, but only if there is a date value in the "Completion" column?

TIA for your help!

Find more posts tagged with

Accepted answers

KPH

You can use an IF function with ISBLANK.

This formula:

=IF(ISBLANK(Completion@row), "", NETDAYS([Start Date]@row, Completion@row))

Says IF Completion@row is blank, return "" (nothing).

If it is not then return the NETDAYS results.

All comments

KPH

You can use an IF function with ISBLANK.

This formula:

=IF(ISBLANK(Completion@row), "", NETDAYS([Start Date]@row, Completion@row))

Says IF Completion@row is blank, return "" (nothing).

If it is not then return the NETDAYS results.

Anjanesh Vaidya

Hi Alexandra Reeder,

You can try the below formula with IFERROR, So, it will not generate an error.

=IFERROR(NETDAYS([Start Date]@row, Completion@row), " ")

Thank you

Anjanesh Vaidya

Alexandra Reeder

@KPH, your formula worked

Thank you, both, for your willingness to help.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.