Networkday Formula Not Working Correctly

Options

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

Tags:

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    @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.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    @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.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!