Resource utilization between two dates

[emfc00]

Hi all,
I have a spreadsheet tracking a project (start and end dates for various tasks) with resources assigned (via the denotation of a 1) for each of those tasks. I've created named ranges for "Start", "End", and each of the resource columns. I would now like to create a calendar of sorts to look for any gaps in resource utilization. To do this I've created rows for each of the resources and each column next to that is a day. I tried to use if/and functions to return a 1 if a resource is allocated on a given day, but all I get are 0's. Here is what I used:

=IF(AND(Resource=1,Start<=D$5,End>=D$5),1,0)

Will this not work with ranges? Should I be using a lookup instead?

Thanks!

 

[StephenCrump]

My first formula was a bit lazy, and assumed the Resources range was either 1 or blank (equivalent to 0). The #VALUE error suggest that's not the case - a single rogue space character would be sufficient to throw an error.

The 2nd formula is more robust, testing explicitly that the cell value = 1.

 

[emfc00]

Brilliant! Thanks for the explanation.

I just realized that this will show me gaps, but not where I might over assign. Is there a way to tweak it so it will instead count how many occurrences meet that match?

For example, if resource A is simultaneously assigned a task from 1-10 January and another task from 8-16 January. Then it should show a 2 for 8, 9 and 10 January. Or am I overreaching capabilities here?

 

[StephenCrump]

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1			Person	Person	Person
2	Start	End	A	B	C
3	1 Jan 2020	10 Jan 2020	1
4	15 Feb 2020	15 Apr 2020		1
5	14 Feb 2020	20 Feb 2020		1	1
6	12 Jan 2020	14 Feb 2020	1
7	20 Feb 2020	29 Feb 2020	1
8	1 Apr 2020	5 May 2020		1
9	16 Apr 2020	30 Apr 2020	1		1
10
11		Person	1 Jan 2020	2 Jan 2020	3 Jan 2020	....	13 Feb 2020	14 Feb 2020	15 Feb 2020	16 Feb 2020	....	14 Apr 2020	15 Apr 2020	16 Apr 2020
12		A	1	1	1		1	1						1
13		B						1	2	2		2	2	1
14		C						1	1	1				1
Sheet3

Cell Formulas
Range	Formula
C12:N14	C12	=IFERROR(1/(1/COUNTIFS(Start,"<="&C$11,End,">="&C$11,INDEX($C$3:$E$9,,MATCH($B12,$C$2:$E$2,)),1)),"")

Named Ranges
Name	Refers To	Cells
End	=Sheet3!$B$3:$B$9	C12:N14
Start	=Sheet3!$A$3:$A$9	C12:N14

 

[emfc00]

Beautiful!! Thanks again!!