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
Need more help? 👀 | 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
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!