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]

Functions like AND, OR, MAX, MEDIAN collapse any array to a single value.

I am guessing you want something like this?

	A	B	C	D
1
2
3
4				TestDate
5				15 Feb 2020
6
7	Resource	Start	End	Result
8	1	1 Jan 2020	31 Mar 2020	1
9	2	15 Feb 2020	15 Apr 2020	0
10	3	12 Jan 2020	22 Feb 2020	0
11	1	1 Feb 2020	29 Feb 2020	1
12	1	1 Apr 2020	5 May 2020	0
13	1	10 Feb 2020	20 Feb 2020	1
Sheet1

Cell Formulas
Range	Formula
D8:D13	D8	=(Resource=1)*(Start<=D$5)*(End>=D$5)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
End	=Sheet1!$C$8:$C$13	D8
Resource	=Sheet1!$A$8:$A$13	D8
Start	=Sheet1!$B$8:$B$13	D8

Is this along the right lines? What do you intend doing with the result? And what version of Excel are you running - the solution shown will display differently if you don't have Excel 365.

 

[emfc00]

Hopefully this makes more sense. I can have multiple (or no) resources assigned to various tasks.

I want to be able to use data from the above input to create a timeline like below, but with 1's where that resource would be working that day. In this way I can look for gaps in coverage.

Sorry for the lack of clarity. I was realizing on the drive home that I should have included some pictures or something.

 

[StephenCrump]

Much clearer, thanks.

I'll post back later, but with a bit of luck, someone else may want to respond in the meantime.

 

[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	2 Jan	3 Jan	....	13 Feb	14 Feb	15 Feb	16 Feb	....	14 Apr	15 Apr	16 Apr
12		A	1	1	1		1	1						1
13		B						1	1	1		1	1	1
14		C						1	1	1				1
Sheet1

Cell Formulas
Range	Formula
C12:N14	C12	=IF(OR((C$11>=Start)*(C$11<=End)*INDEX($C$3:$E$9,,MATCH($B12,$C$2:$E$2,))),1,"")

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

 

[emfc00]

	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	2 Jan	3 Jan	....	13 Feb	14 Feb	15 Feb	16 Feb	....	14 Apr	15 Apr	16 Apr
12		A	1	1	1		1	1						1
13		B						1	1	1		1	1	1
14		C						1	1	1				1
Sheet1

Cell Formulas
Range	Formula
C12:N14	C12	=IF(OR((C$11>=Start)*(C$11<=End)*INDEX($C$3:$E$9,,MATCH($B12,$C$2:$E$2,))),1,"")

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

Thank you for all of the help thus far! Fairly certain I did this correct, but I am getting an error. I have D7 highlighted. I used new named ranges for "Resources" (K6:T111 on previous picture) and "Title" (K4:T4 in previous picture).

 

[StephenCrump]

See if array-entering the formula (CTRL-SHIFT-ENTER) makes a difference?

Sorry, I can only test Excel 365 on this machine, and I'm rapidly forgetting when array-enter was needed in previous Excel versions.

 

[emfc00]

I can get it to work by perfectly by mimicking exactly what you displayed earlier and am using 365. But when I try it with my data it produces the error.

Test.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN
3			Start	End	Resources
4	ﾠ		1/4/2021	2/4/2023	T Cnslt 1	T Cnslt 2	Cnslt 1	Cnslt 2	Editor-ILT	Director			1/4/2021	1/5/2021	1/6/2021	1/7/2021	1/8/2021	1/11/2021	1/12/2021	1/13/2021	1/14/2021	1/15/2021	1/19/2021	1/20/2021	1/21/2021	1/22/2021	1/25/2021	1/26/2021	1/27/2021	1/28/2021	1/29/2021	2/1/2021	2/2/2021	2/3/2021	2/4/2021	2/5/2021	2/8/2021	2/9/2021	2/10/2021	2/11/2021	2/12/2021	2/16/2021	2/17/2021	2/18/2021	2/19/2021	2/22/2021	2/23/2021	2/24/2021	2/25/2021	2/26/2021	3/1/2021	3/2/2021	3/3/2021	3/4/2021	3/5/2021	3/8/2021	3/9/2021	3/10/2021	3/11/2021	3/12/2021	3/15/2021	3/16/2021	3/17/2021	3/18/2021	3/19/2021
5	1ﾠ		1/4/2021	2/4/2023								T Cnslt 1	#VALUE!
6	1.1ﾠ		1/4/2021	2/4/2023						1		T Cnslt 2	#VALUE!
7	1.2ﾠ		1/4/2021	9/24/2021								Cnslt 1	#VALUE!
8	1.2.1ﾠ		1/4/2021	6/28/2021		1	1					Cnslt 2	#VALUE!
9	1.2.2ﾠ		6/29/2021	9/24/2021		1	1					Editor-ILT	#VALUE!
10	1.3ﾠ		9/27/2021	10/12/2021								Director	#VALUE!
11	1.3.1ﾠ		9/27/2021	9/29/2021		1	1
12	1.3.2ﾠ		9/30/2021	10/4/2021		1	1
13	1.3.3ﾠ		10/5/2021	10/7/2021		1	1
14	1.3.4ﾠ		10/8/2021	10/12/2021		1	1
15	1.4ﾠ		10/13/2021	12/17/2021
16	1.4.1		10/13/2021	11/1/2021		1	1
17	1.4.2		11/2/2021	11/10/2021		1	1
18	1.4.3		11/11/2021	12/2/2021		1	1
19	1.4.4		10/13/2021	11/5/2021	1			1
20	1.4.5		11/8/2021	11/19/2021	1			1
21	1.4.6		11/22/2021	12/2/2021	1			1
22	1.4.7		12/3/2021	12/17/2021
23	1.4.7.1ﾠ		12/3/2021	12/7/2021		1	1
24	1.4.7.2ﾠ		12/8/2021	12/10/2021		1	1
25	1.4.7.3ﾠ		12/13/2021	12/15/2021		1	1
26	1.4.7.4ﾠ		12/16/2021	12/17/2021		1	1
27	1.4.7.5ﾠ		12/3/2021	12/8/2021	1			1
28	1.4.7.6ﾠ		12/9/2021	12/14/2021	1			1
29	1.4.7.7ﾠ		12/15/2021	12/17/2021	1			1
30	1.5ﾠ		12/17/2021	12/17/2021
Est. Development

Cell Formulas
Range	Formula
O4:BN4	O4	=WORKDAY(N4,1,Holidays)
N4	N4	='Est. Development'!D6
N5:N10	N5	=IF(OR((N$4>=Start)*(N$4<=End)*INDEX(Resource,,MATCH($M5,Title,))),1,"")

Named Ranges
Name	Refers To	Cells
End	='Est. Development'!$E$6:$E$30	N5:N10
Holidays	=Holidays!$E$8:$G$27	O4:BN4
Resource	='Est. Development'!$F$6:$K$30	N5:N10
Start	='Est. Development'!$D$6:$D$30	N4:N10
Title	='Est. Development'!$F$4:$K$4	N5:N10

 

[StephenCrump]

Does it make a difference with:

N5: =IF(OR((N$4>=Start)*(N$4<=End)*(INDEX(Resource,,MATCH($M5,Title,))=1)),1,"")

 

[emfc00]

Does it make a difference with:

N5: =IF(OR((N$4>=Start)*(N$4<=End)*(INDEX(Resource,,MATCH($M5,Title,))=1)),1,"")

That did the trick! What is the difference that would require that slight alteration? Thank you so much... you made my night!