Duration to a specific task from project start / counting the number of project days



I'm wondering if there is a function in Smartsheet by which I can create a column that displays the total number of project days from the beginning of the project ("Project Day 1") up to the beginning of each task. For example, the first task would start on "project day 1," the last task of the project would start on "project day 265" (or however long the total duration of the entire planned project is). Note: this is not the duration of an individual task, but the "project day," as in the project day number in the total life cycle of the project.

Related: I'd like to display these "Project Days" while also taking into account "weekends" and "holidays" which are NOT considered project days. We work Mon-Sat, so it would be 6 project days per week (not counting holidays).



Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You would need to adjust your settings to reflect Mon-Sat and update the holiday dates (either in your settings, somewhere on the sheet, or in a different sheet). Then you can use a NETWORKDAYS function (link included) like so:

    =NETWORKDAYS(DATE(2020, 08, 18), [Start Date]@row)

    If you have a cell containing the date:

    =NETWORKDAYS([Start Date]$1, [Start Date]@row)

    If your holidays are in a sheet instead of in your settings:

    =NETWORKDAYS([Start Date]$1, [Start Date]@row, [Holiday Column]:[Holiday Column])


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!