Networkday Formula Not Working Correctly

I'm using the networkday formula to gather the number of working days between two dates and then dividing that number by 5 to get the total number of weeks. However, the formula doesn't seem to provide the correct answer unless I change the dividing number to 4.7 (no idea why that would work since there's 5 days in a typical workweek). I've posted my formula below (I'm trying to get the number of hours a week a resource will work on a task).

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, SUM(CHILDREN()), IF(AND([Start Date]@row <= DATE(2023, 1, 28), [End Date]@row >= DATE(2023, 1, 22)), [Hours per Task]@row / (NETWORKDAYS([Start Date]@row, [End Date]@row) / 5), IF(AND([Start Date]@row <= DATE(2023, 1, 28), [End Date]@row >= DATE(2023, 1, 22)), [Hours per Task]@row / (NETWORKDAYS([Start Date]@row, [End Date]@row) / 5), "")))

Find more posts tagged with

Formulas

Accepted answers

Jeff Reisman

@Sarah123

If there's a holiday that falls between your start and end dates, that could affect your math results, because there would only be 4 days in that particular work week.

You might try using the WEEKNUMBER function. Subtract the WEEKNUMBER([Start Date]@row) from the WEEKNUMBER([End Date]@row) and you get a pretty accurate idea of how many weeks.

You can tweak this to only include complete weeks by using the WEEKDAY function, for instance to say if the WEEKDAY(1 for Sunday, 2 for Monday, etc) of the End Date is less than the WEEKDAY of the Start Date, to subtract 1 from the difference between the end week and the start week.

All comments

Jeff Reisman

@Sarah123

If there's a holiday that falls between your start and end dates, that could affect your math results, because there would only be 4 days in that particular work week.

You might try using the WEEKNUMBER function. Subtract the WEEKNUMBER([Start Date]@row) from the WEEKNUMBER([End Date]@row) and you get a pretty accurate idea of how many weeks.

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.