Remy L
Remy L ✭✭
edited 02/29/24 in Formulas and Functions

Feel free to duplicate this yourself to see the problem:

I am using manually created durations in order to calculate the finish date and determine if a task could run late if it takes as long as forecasted.

The formula in Est. Duration is:

=NETWORKDAYS([Est. Start]@row, [Est. Finish]@row)

The formula in estDuration is:

=[Est. Duration]@row

The formula in calcFinish is:

=WORKDAY([Start Date]@row, VALUE(estDuration@row))

I need the helper for estDuration in order to convert Est. Duration to a number.

But if you look at the values, they are inconsistent. I understand that NETWORKDAYS includes the start date. In that case, "27" makes sense. But in going back the other way, with WORKDAY I should subtract 1.

However, look at the second row. It calculates the duration correctly AND it calculate the finish date correctly going back the other way.



  • KPH
    KPH ✭✭✭✭✭✭
    edited 02/29/24

    The discrepancy is because you have non work days included in your start and end dates.

    Row 1 start date is a Tuesday and is included in the networkdays between then and finish date of March 13, also a Tuesday. Adding networkdays to start date therefore gives you the finish date plus 1. As expected.

    Row 2 start date is a Sunday, so does not get included in networkdays. Therefore there are only 3 working days between start and finish. 3 working days after Sunday is Monday 26, Tuesday 27, Wednesday 28. Adding networkdays to the start gives you the original finish date (no plus 1 as start date was not counted).

    Row 3 start date is a Wednesday so is included in networkdays (like row 1). However, the finish day is a Saturday so not included. Adding the networkdays to the start date will therefore give you the original finish date as in row 2 (no plus 1, as the finishing Saturday is not included). However, as that is a Saturday the formula returns the next working day which is the Monday (11th).

    Row 4 follows the same pattern as row 1.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!