Finding a value between two date ranges in Google Sheets

Question

I have constructed a resource planner in Google Sheets with project start and end dates against different resources with the output looking something like Attachment1:

From this output I now want to measure in respect of the current day (Today() = 21/06/2017 in this example) whether that resource is currently engaged on a project or not. So that in a separate table I produce an output like Attachment2:

I have attempted to use the a MAX IF formula to do this by following the below process...

First find an end date greater than today for that resource (if null then "N")

Then find start dates for those end dates that are less than today (if null then "N" ELSE "Y")

The formula I used can be seen here (where D31 is the Resource name used to lookup in Attachment2 to Attachment1, $B$3:$B$6 is Attachment1 Resource Name, $D$3:$D$6 is End Date, $C$3:$C$6 is Start Date):

=ArrayFormula(IF(AND(D31 = $B$3:$B$6, $D$3:$D$6 <TODAY()), "N",  IF(AND(D31 = $B$3:$B$6, $C$3:$C$6 >TODAY()), "N", "Y")))

This always returns the exception value of "Y' even when a resource should be engaged, so is incorrect. I have also looked in to using SQL through the google query language but still find errors when attempting to use CASE functionality through this.

How best do I achieve the desired result?

Answer 1

Try it with COUNTIFS (normal, not array formula):

=IF(COUNTIFS($B$3:$B$6,D31, $C$3:$C$6, "<="&TODAY(),$D$3:$D$6, ">="&TODAY())=0,"N","Y")