Adding NETWORKDAYS to a formula gives #INVALID DATA TYPE

A_C

Hi,

Happy Friday!!

Please see below - I know I'm missing something simple. Any thoughts?

I have a little formula that allows me to have a col formula and also have SUM(CHILDREN()).

Date cols are both dates.

Target total duration col is text / number.

When I don't use NETWORKDAYS, I get the total duration as expected, please see the formula and snip below.

=IF([HC-Calc]@row = 1, "", IF(COUNT(CHILDREN()) = 0, [Target End Date]@row - [Target Start Date]@row, SUM(CHILDREN())))

When I update the formula to include NETWORKDAYS, to give the work day duration, and update the start / end date in the formula, I get our old friend #INVALID DATA TYPE. Please see the formula and snip below.

=IF([HC-Calc]@row = 1, "", IF(COUNT(CHILDREN()) = 0, NETWORKDAYS([Target Start Date]@row, [Target End Date]@row, SUM(CHILDREN()))))

Thanks for any help.

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Double and triple check your two date columns are in fact set as date type columns. If they definitely are, how exactly are they being populated?

A_C

Hi,

After finding this post (Thanks @Kelly Moore @Xavier Gomez )

https://community.smartsheet.com/discussion/98691/networkday-shows-invalid-data-type-but-prefer-to-show-0-or-blank

I think I have fixed it with

=IF([HC-Calc]@row = 1, "", IF(COUNT(CHILDREN()) > 0, SUM(CHILDREN()), IF(AND(ISDATE([Target Start Date]@row), ISDATE([Target End Date]@row)), NETWORKDAYS([Target Start Date]@row, [Target End Date]@row))))

A_C

@Paul Newcome Hi, they are date cols, and they will be keyed in by the planners.

The revised formula looks like it works.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.