Converting a list of date ranges into weekly distribution array

[vpandey4]

Hi,

I have a list of data with employees and their holiday data ranges, which I want to transform into an array with weeks (showing week ending date - Friday) to show days of absence in that week

Employee	Start	End	Week Ending -->	03-May​	10-May​	17-May​
E01	30-Apr​	15-May​		4​	5​	3​
E02	08-May​	09-May​		0​	2​	0​
E03	10-May​	13-May​		0​	1​	1​
E04	07-May​	07-May​		0​	1​	0​

Is it possible to get it with some easy formula? I tried with a combination of nested Ifs but became too long and was wondering if I may overlooking an easy solution.
Please suggest.

 

[KRice]

Give this a try:

MrExcel_20220607.xlsx
	A	B	C	D	E	F	G
1	Employee	Start	End	Week Ending -->	5/3/2019	5/10/2019	5/17/2019
2	E01	4/30/2019	5/15/2019		4	5	3
3	E02	5/8/2019	5/9/2019		0	2	0
4	E03	5/10/2019	5/13/2019		0	1	1
5	E04	5/7/2019	5/7/2019		0	1	0
Sheet3

Cell Formulas
Range	Formula
E2:G5	E2	=IF(AND(WEEKNUM(E$1,16)>=WEEKNUM($B2,16),WEEKNUM(E$1,16)<=WEEKNUM($C2,16)),MIN(E$1,$C2)-MAX(E$1-4,$B2)+1,0)

...or the slightly simpler version without the IF function:

=AND(WEEKNUM(E$1,16)>=WEEKNUM($B2,16),WEEKNUM(E$1,16)<=WEEKNUM($C2,16))*(MIN(E$1,$C2)-MAX(E$1-4,$B2)+1)

 

[vpandey4]

Give this a try:

MrExcel_20220607.xlsx
	A	B	C	D	E	F	G
1	Employee	Start	End	Week Ending -->	5/3/2019	5/10/2019	5/17/2019
2	E01	4/30/2019	5/15/2019		4	5	3
3	E02	5/8/2019	5/9/2019		0	2	0
4	E03	5/10/2019	5/13/2019		0	1	1
5	E04	5/7/2019	5/7/2019		0	1	0
Sheet3

Cell Formulas
Range	Formula
E2:G5	E2	=IF(AND(WEEKNUM(E$1,16)>=WEEKNUM($B2,16),WEEKNUM(E$1,16)<=WEEKNUM($C2,16)),MIN(E$1,$C2)-MAX(E$1-4,$B2)+1,0)

...or the slightly simpler version without the IF function:

=AND(WEEKNUM(E$1,16)>=WEEKNUM($B2,16),WEEKNUM(E$1,16)<=WEEKNUM($C2,16))*(MIN(E$1,$C2)-MAX(E$1-4,$B2)+1)

Hi @KRice : Thanks- It works for a data set of 01 year but since my data goes through recursively for more than a year- WEEKNUM returns TRUE condition for the next year range as well. Any idea how to control that?

 

[vpandey4]

Hi @KRice : Thanks- It works for a data set of 01 year but since my data goes through recursively for more than a year- WEEKNUM returns TRUE condition for the next year range as well. Any idea how to control that?

I tried appending TEXT(cell,"yy")&WEEKNUM(xyz) to solve this... but please suggest if you have a better solution

 

[KRice]

I think starting over might be better. And rather than trying to determine if we are in a week that matters, the formula should return a nonsense answer that can be trapped. Try this:

MrExcel_20220607.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Start	End	Week Ending -->	5/4/2018	5/11/2018	5/18/2018	5/3/2019	5/10/2019	5/17/2019	12/27/2019	1/3/2020	1/10/2020	5/1/2020	5/8/2020	5/15/2020
2	4/30/2019	5/15/2019		0	0	0	4	5	3	0	0	0	0	0	0
3	5/10/2018	5/16/2018		0	2	3	0	0	0	0	0	0	0	0	0
4	5/10/2019	5/13/2019		0	0	0	0	1	1	0	0	0	0	0	0
5	5/9/2019	5/13/2019		0	0	0	0	2	1	0	0	0	0	0	0
6	12/24/2019	1/8/2020		0	0	0	0	0	0	4	5	3	0	0	0
7	5/2/2018	5/17/2018		3	5	4	0	0	0	0	0	0	0	0	0
8	5/2/2020	5/11/2020		0	0	0	0	0	0	0	0	0	0	5	1
Sheet3

Cell Formulas
Range	Formula
E2:P8	E2	=MAX(MIN(MIN($C2,$C2+7-WEEKDAY($C2+7-6)),E$1)-MAX($B2,E$1-4)+1,0)

 

[vpandey4]

I think starting over might be better. And rather than trying to determine if we are in a week that matters, the formula should return a nonsense answer that can be trapped. Try this:

MrExcel_20220607.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Start	End	Week Ending -->	5/4/2018	5/11/2018	5/18/2018	5/3/2019	5/10/2019	5/17/2019	12/27/2019	1/3/2020	1/10/2020	5/1/2020	5/8/2020	5/15/2020
2	4/30/2019	5/15/2019		0	0	0	4	5	3	0	0	0	0	0	0
3	5/10/2018	5/16/2018		0	2	3	0	0	0	0	0	0	0	0	0
4	5/10/2019	5/13/2019		0	0	0	0	1	1	0	0	0	0	0	0
5	5/9/2019	5/13/2019		0	0	0	0	2	1	0	0	0	0	0	0
6	12/24/2019	1/8/2020		0	0	0	0	0	0	4	5	3	0	0	0
7	5/2/2018	5/17/2018		3	5	4	0	0	0	0	0	0	0	0	0
8	5/2/2020	5/11/2020		0	0	0	0	0	0	0	0	0	0	5	1
Sheet3

Cell Formulas
Range	Formula
E2:P8	E2	=MAX(MIN(MIN($C2,$C2+7-WEEKDAY($C2+7-6)),E$1)-MAX($B2,E$1-4)+1,0)

Thank you!!