Formula

Filippo01

'm trying to create a formula for the below information:

find if in a range of start dates and end dates of a specific job, one or more of these dates correspond to the current week date

if that's happen the guy is not available otherwise is available for this week

thanks

Genevieve P.

Hi @Filippo01

Assuming this is looking across sheets, the way I would do this is to use a COUNTIFS Function to count how many rows in your first sheet have a row that passes over your current date:

COUNTIFS({Start Date Range}, <=[Current Week Date]@row, {Finish Dates}, >=[Current Week Date]@row)

Then if the COUNT returned is greater than 0 (meaning at least one row has that date associated with it), you can use an IF statement to return text or a number, etc.

=IF(COUNTIFS({Start Date Range}, <=[Current Week Date]@row, {Finish Dates}, >=[Current Week Date]@row) > 0, "Not Available", "Available")

Let me know if this makes sense and will work for you!

Cheers,

Genevieve