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

John Paul Stepanian

Hello,

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

Thanks!

JP

Paul Newcome

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])

Paul Newcome

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])

John Paul Stepanian

Thank you, it worked! Just note: column type must be "text/number" and not "date." Best, JP

Paul Newcome

Happy to help. 👍️

And yes. Since the NETWORKDAYS function generates a numeric value it will need to go into a text/number type column.