Networkday Formula Not Working Correctly

Sarah123

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), "")))

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.

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.