Availability Formula
how can i calculate through a formula the availability of a person for the current week considering the range of all the start dates and end dates of the jobs in which he is envolved?
=IF(WEEKNUMBER([Start Date]@row) = WEEKNUMBER(TODAY()), "not available ", IF(WEEKNUMBER([End Date]@row) = WEEKNUMBER(TODAY()), "not available", "available"))
actually i use this formula but the problem is that this formula works just with one start date and one end date, while i have to consider a range of start dates and end dates
thanks
Answers
-
Try using a COUNTIFS to evaluate the start date column counting how many rows there are that have the same week number. Use a COUNTIFS for the end date the same way. If you add those two together and the outcome is more than zero, then there is at least one row where there is an issue.
=IF(COUNTIFS([Start Date]:[Start Date], ...............) + COUNTIFS([End Date]:[End Date], ................)> 0, "Not Available", "Available")
-
@Paul Newcome thanks for helping
are you able to write me the complete formula?
-
-
Hi @Filippo01
I believe I answered your question in this other thread, here:
What you need to write in the "...." will depend on what your criteria is: what are you looking for? Is it a specific person or is it just the weeknumber?
For example:
COUNTIFS([Start Date]:[Start Date], WEEKNUMBER(TODAY()))
Cheers,
Genevieve
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 381 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 291 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!