Can I calculate the available weeks (with no tasks) in a gantt chart?

Mica

Hi Community!

I have created a Gantt chart of the team workload- for each member I mention all the projects they are working on across the year.

I would like to know if there is an option or a formula in Smartsheets to indicate for each team member the 'available' weeks / with no projects allocated in that year (I can see it in the Gantt chart the empty weeks but I would like to know the number too).

I hope you can help me out with this!

you are awesome =)

Genevieve P.

Hi @Mica

The way I would do this is to create a reference sheet that lists out each of the weeks you want to track for the year, with two date columns, Start and End.

Then have one column per member, and you can use a cross-sheet COUNTIFS function looking into your Project sheet to see if each person is scheduled within that week.

=COUNTIFS({Assigned To}, "Genevieve P.", {Start Date}, >=[Start Date]@row, {Start Date}, <=[End Date]@row)

In the top row, I've then used a simple COUNTIF to see in the current column how many of my weeks show as 0, so they're available:

=COUNTIF(Geneveive:Genevieve, 0)

Another option may be to look into using the Legacy Resource Management function in Smartsheet, however I would recommend taking a look at Resource Management by Smartsheet instead if you wanted to go down that route. See: Intro to Resource Management by Smartsheet

Cheers,

Genevieve