Availability Formula

Filippo01

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

Paul Newcome

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

Filippo01

@Paul Newcome thanks for helping

are you able to write me the complete formula?

Filippo01

I @Paul Newcome what i have to write in the ........?

i don't understand

thanks

Genevieve P.

Hi @Filippo01

I believe I answered your question in this other thread, here:

https://community.smartsheet.com/discussion/94856/formula

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