Can I calculate the available weeks (with no tasks) in a gantt chart?
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 =)
Answers
-
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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!