Day calendar referencing array with duplicates

[joercav]

Any help would be appreciated. I have a dynamic table of dates. Each column references entire production cycles and each row the cycle stages, with the top row being the start and the bottom the end. I am trying to create a "day calendar" on new sheet with the first column listing every day from today and 10 years into the future. To the right of each day, I'd like to show which production cycle and stage is active. On any given day there will be a handful active at the same time. A solution would be helpful because each cycle-stage has inputs that can be listed as well and sums made.

Cav Farm Management.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1		14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34
2	1	4/10/21	8/29/21	12/8/21	4/17/22	7/24/22	10/23/22	12/2/22	12/30/22	3/10/23	4/24/23	6/8/23	8/2/23	8/30/23	10/25/23	12/9/23	1/22/24	2/21/24	3/22/24	4/21/24	5/21/24	6/20/24
3	2	4/12/21	8/31/21	12/10/21	4/19/22	7/26/22	10/25/22	12/4/22	1/1/23	3/12/23	4/26/23	6/10/23	8/4/23	9/1/23	10/27/23	12/11/23	1/24/24	2/23/24	3/24/24	4/23/24	5/23/24	6/22/24
4	3	4/24/21	9/12/21	12/22/21	5/1/22	8/7/22	11/6/22	12/16/22	1/13/23	3/24/23	5/8/23	6/22/23	8/16/23	9/13/23	11/8/23	12/23/23	2/5/24	3/6/24	4/5/24	5/5/24	6/4/24	7/4/24
5	4	5/1/21	9/19/21	12/29/21	5/8/22	8/14/22	11/13/22	12/23/22	1/20/23	3/31/23	5/15/23	6/29/23	8/23/23	9/20/23	11/15/23	12/30/23	2/12/24	3/13/24	4/12/24	5/12/24	6/11/24	7/11/24
6	5	5/4/21	9/19/21	12/29/21	5/8/22	8/14/22	11/13/22	12/23/22	1/20/23	3/31/23	5/15/23	6/29/23	8/23/23	9/20/23	11/15/23	12/30/23	2/12/24	3/13/24	4/12/24	5/12/24	6/11/24	7/11/24
7	6	6/28/21	11/7/21	2/22/22	7/2/22	10/8/22	1/7/23	2/16/23	3/16/23	5/25/23	7/9/23	8/23/23	10/17/23	11/14/23	1/9/24	2/23/24	4/7/24	5/7/24	6/6/24	7/6/24	8/5/24	9/4/24
8	7	8/18/21	1/6/22	4/17/22	8/25/22	12/1/22	3/2/23	4/11/23	5/9/23	7/18/23	9/1/23	10/16/23	12/10/23	1/7/24	3/3/24	4/17/24	5/31/24	6/30/24	7/30/24	8/29/24	9/28/24	10/28/24
9	8	8/26/21	1/13/22	4/24/22	9/1/22	12/8/22	3/9/23	4/18/23	5/16/23	7/25/23	9/8/23	10/23/23	12/17/23	1/14/24	3/10/24	4/24/24	6/7/24	7/7/24	8/6/24	9/5/24	10/5/24	11/4/24
10	9	9/16/21	2/3/22	5/15/22	9/22/22	12/29/22	3/30/23	5/9/23	6/6/23	8/15/23	9/29/23	11/13/23	1/7/24	2/4/24	3/31/24	5/15/24	6/28/24	7/28/24	8/27/24	9/26/24	10/26/24	11/25/24
11	10	9/23/21	2/14/22	5/26/22	10/3/22	1/9/23	4/10/23	5/20/23	6/17/23	8/26/23	10/10/23	11/24/23	1/18/24	2/15/24	4/11/24	5/26/24	7/9/24	8/8/24	9/7/24	10/7/24	11/6/24	12/6/24
12	11	11/11/21	3/31/22	7/10/22	11/17/22	2/23/23	5/25/23	7/4/23	8/1/23	10/10/23	11/24/23	1/8/24	3/3/24	3/31/24	5/26/24	7/10/24	8/23/24	9/22/24	10/22/24	11/21/24	12/21/24	1/20/25
13	12	11/18/21	4/7/22	7/17/22	11/24/22	3/2/23	6/1/23	7/11/23	8/8/23	10/17/23	12/1/23	1/15/24	3/10/24	4/7/24	6/2/24	7/17/24	8/30/24	9/29/24	10/29/24	11/28/24	12/28/24	1/27/25
14	13	11/25/21	4/14/22	7/24/22	12/1/22	3/9/23	6/8/23	7/18/23	8/15/23	10/24/23	12/8/23	1/22/24	3/17/24	4/14/24	6/9/24	7/24/24	9/6/24	10/6/24	11/5/24	12/5/24	1/4/25	2/3/25
15	14	4/10/21	8/29/21	12/8/21	4/17/22	7/24/22	10/23/22	12/2/22	12/30/22	3/10/23	4/24/23	6/8/23	8/2/23	8/30/23	10/25/23	12/9/23	1/22/24	2/21/24	3/22/24	4/21/24	5/21/24	6/20/24
16	15	5/1/21	9/19/21	12/29/21	5/8/22	8/14/22	11/13/22	12/23/22	1/20/23	3/31/23	5/15/23	6/29/23	8/23/23	9/20/23	11/15/23	12/30/23	2/12/24	3/13/24	4/12/24	5/12/24	6/11/24	7/11/24
17	16	12/9/21	4/28/22	8/7/22	12/15/22	3/23/23	6/22/23	8/1/23	8/29/23	11/7/23	12/22/23	2/5/24	3/31/24	4/28/24	6/23/24	8/7/24	9/20/24	10/20/24	11/19/24	12/19/24	1/18/25	2/17/25
18	17	10/7/21	2/28/22	6/9/22	10/17/22	1/23/23	4/24/23	6/3/23	7/1/23	9/9/23	10/24/23	12/8/23	2/1/24	2/29/24	4/25/24	6/9/24	7/23/24	8/22/24	9/21/24	10/21/24	11/20/24	12/20/24
19	18	12/16/21	5/5/22	8/14/22	12/22/22	3/30/23	6/29/23	8/8/23	9/5/23	11/14/23	12/29/23	2/12/24	4/7/24	5/5/24	6/30/24	8/14/24	9/27/24	10/27/24	11/26/24	12/26/24	1/25/25	2/24/25
20	19	1/13/22	6/2/22	9/11/22	1/19/23	4/27/23	7/27/23	9/5/23	10/3/23	12/12/23	1/26/24	3/11/24	5/5/24	6/2/24	7/28/24	9/11/24	10/25/24	11/24/24	12/24/24	1/23/25	2/22/25	3/24/25
21	20	2/10/22	6/30/22	10/9/22	2/16/23	5/25/23	8/24/23	10/3/23	10/31/23	1/9/24	2/23/24	4/8/24	6/2/24	6/30/24	8/25/24	10/9/24	11/22/24	12/22/24	1/21/25	2/20/25	3/22/25	4/21/25
22	21	3/10/22	7/28/22	11/6/22	3/16/23	6/22/23	9/21/23	10/31/23	11/28/23	2/6/24	3/22/24	5/6/24	6/30/24	7/28/24	9/22/24	11/6/24	12/20/24	1/19/25	2/18/25	3/20/25	4/19/25	5/19/25
23	22	4/7/22	8/25/22	12/4/22	4/13/23	7/20/23	10/19/23	11/28/23	12/26/23	3/5/24	4/19/24	6/3/24	7/28/24	8/25/24	10/20/24	12/4/24	1/17/25	2/16/25	3/18/25	4/17/25	5/17/25	6/16/25
24	23	5/5/22	9/22/22	1/1/23	5/11/23	8/17/23	11/16/23	12/26/23	1/23/24	4/2/24	5/17/24	7/1/24	8/25/24	9/22/24	11/17/24	1/1/25	2/14/25	3/16/25	4/15/25	5/15/25	6/14/25	7/14/25
25	24	6/2/22	10/20/22	1/29/23	6/8/23	9/14/23	12/14/23	1/23/24	2/20/24	4/30/24	6/14/24	7/29/24	9/22/24	10/20/24	12/15/24	1/29/25	3/14/25	4/13/25	5/13/25	6/12/25	7/12/25	8/11/25
Sheet1

 

[Eric W]

What version of Excel do you have? If you have Excel 365, or Excel 2021, then try:

Book1
	A	B	C
1	Date	Stages
2	4/10/2021	Cycle 14, Stage 1: Cycle 14, Stage 14
3	4/12/2021	Cycle 14, Stage 2
4	4/24/2021	Cycle 14, Stage 3
5	5/1/2021	Cycle 14, Stage 4: Cycle 14, Stage 15
6	5/4/2021	Cycle 14, Stage 5
7	6/28/2021	Cycle 14, Stage 6
8	8/18/2021	Cycle 14, Stage 7
9	8/26/2021	Cycle 14, Stage 8
10	8/29/2021	Cycle 15, Stage 1: Cycle 15, Stage 14
11	8/31/2021	Cycle 15, Stage 2
12	9/12/2021	Cycle 15, Stage 3
13	9/16/2021	Cycle 14, Stage 9
14	9/19/2021	Cycle 15, Stage 4: Cycle 15, Stage 5: Cycle 15, Stage 15
15	9/23/2021	Cycle 14, Stage 10
Sheet3

Cell Formulas
Range	Formula
A2:A362	A2	=UNIQUE(SMALL(Sheet2!B2:V25,SEQUENCE(COUNT(Sheet2!B2:V25))))
B2:B15	B2	=TEXTJOIN(": ",1,IF(Sheet2!$B$2:$V$25=A2,"Cycle "&Sheet2!$B$1:$V$1&", Stage "&Sheet2!$A$2:$A$25,""))
Dynamic array formulas.

If you have Excel 2019, I can adapt this. Anything older than that will probably require VBA.

 

[joercav]

Thanks for the help! I have 365 ver 16.58 running on mac. Could you help some more? I'd like to have each and every day listed:

Cav Farm Management.xlsx
	A	B
5	1/4/22	13-5
6	1/5/22	13-5
7	1/6/22	14-4; 13-5
8	1/7/22	14-4; 13-5
9	1/8/22	14-4; 13-5
10	1/9/22	14-4; 13-5
11	1/10/22	14-4; 13-5
Calendar

 

[Eric W]

You mean every day, including the days that don't have a stage on it? Try:

Book1
	A	B
1	Date	Stages
2	4/10/2021	14-1; 14-14
3	4/11/2021
4	4/12/2021	14-2
5	4/13/2021
6	4/14/2021
7	4/15/2021
8	4/16/2021
9	4/17/2021
10	4/18/2021
11	4/19/2021
12	4/20/2021
13	4/21/2021
14	4/22/2021
15	4/23/2021
16	4/24/2021	14-3
17	4/25/2021
18	4/26/2021
19	4/27/2021
20	4/28/2021
21	4/29/2021
22	4/30/2021
23	5/1/2021	14-4; 14-15
24	5/2/2021
25	5/3/2021
26	5/4/2021	14-5
27	5/5/2021
28	5/6/2021
29	5/7/2021
30	5/8/2021
Sheet3

Cell Formulas
Range	Formula
A2:A1586	A2	=SEQUENCE(MAX(Sheet2!B2:V25)-MIN(Sheet2!B2:V25)+1,,MIN(Sheet2!B2:V25))
B2:B30	B2	=TEXTJOIN("; ",1,IF(Sheet2!$B$2:$V$25=A2,Sheet2!$B$1:$V$1&"-"&Sheet2!$A$2:$A$25,""))
Dynamic array formulas.

 

[joercav]

Yes. But the days that are showing empty cells actually have ongoing cycle stages. The first row of each column is the start of the cycle with the initial stage #1, that stage runs until #2 starts. The bottom row stage #24 marks the end of the entire cycle and has no subsequent days.
Thanks for your help on this!

 

[Eric W]

OK, try:

Book1
	A	B
1	Date	Stages
2	4/10/2021	14-1; 14-14
3	4/11/2021	14-1; 14-14
4	4/12/2021	14-2; 14-14
5	4/13/2021	14-2; 14-14
6	4/14/2021	14-2; 14-14
7	4/15/2021	14-2; 14-14
8	4/16/2021	14-2; 14-14
9	4/17/2021	14-2; 14-14
10	4/18/2021	14-2; 14-14
Sheet3

Cell Formulas
Range	Formula
A2:A1586	A2	=SEQUENCE(MAX(Sheet2!B2:V25)-MIN(Sheet2!B2:V25)+1,,MIN(Sheet2!B2:V25))
B2:B10	B2	=TEXTJOIN("; ",1,IF((A2>=Sheet2!$B$2:$V$25)*(A2<Sheet2!$B$3:$V$26),Sheet2!$B$1:$V$1&"-"&Sheet2!$A$2:$A$25,""))
Dynamic array formulas.

Some of the later rows are quite large though:

Book1
	A	B
1581	8/6/2025	34-23; 14-24; 15-24; 16-24; 17-24; 18-24; 19-24; 20-24; 21-24; 22-24; 23-24; 24-24; 25-24; 26-24; 27-24; 28-24; 29-24; 30-24; 31-24; 32-24; 33-24
1582	8/7/2025	34-23; 14-24; 15-24; 16-24; 17-24; 18-24; 19-24; 20-24; 21-24; 22-24; 23-24; 24-24; 25-24; 26-24; 27-24; 28-24; 29-24; 30-24; 31-24; 32-24; 33-24
1583	8/8/2025	34-23; 14-24; 15-24; 16-24; 17-24; 18-24; 19-24; 20-24; 21-24; 22-24; 23-24; 24-24; 25-24; 26-24; 27-24; 28-24; 29-24; 30-24; 31-24; 32-24; 33-24
1584	8/9/2025	34-23; 14-24; 15-24; 16-24; 17-24; 18-24; 19-24; 20-24; 21-24; 22-24; 23-24; 24-24; 25-24; 26-24; 27-24; 28-24; 29-24; 30-24; 31-24; 32-24; 33-24
1585	8/10/2025	34-23; 14-24; 15-24; 16-24; 17-24; 18-24; 19-24; 20-24; 21-24; 22-24; 23-24; 24-24; 25-24; 26-24; 27-24; 28-24; 29-24; 30-24; 31-24; 32-24; 33-24
1586	8/11/2025	14-24; 15-24; 16-24; 17-24; 18-24; 19-24; 20-24; 21-24; 22-24; 23-24; 24-24; 25-24; 26-24; 27-24; 28-24; 29-24; 30-24; 31-24; 32-24; 33-24; 34-24
Sheet3

Cell Formulas
Range	Formula
B1581:B1586	B1581	=TEXTJOIN("; ",1,IF((A1581>=Sheet2!$B$2:$V$25)*(A1581<Sheet2!$B$3:$V$26),Sheet2!$B$1:$V$1&"-"&Sheet2!$A$2:$A$25,""))

 

[joercav]

Thanks for your help. But that doesnt look right to me. Using this sheet of dates I first posted, on 8/6/25, there should only be 34-24, because all other stages ended on their "end" day (25) and should not show up ever again. On 8/11/25, Cycle 34 hits its "end" day (25) and should be 34-25, and there after cycle 34 won't show again.
If you can find a solution, I'd be super appreciative- Thank you!

 

[Eric W]

Whoops! Sorry, I missed your comment where you said there are no days past stage 24, and I just used a very large date. Try this:

Book1
	A	B
1	Date	Stages
2	4/10/2021	14-1; 14-14
3	4/11/2021	14-1; 14-14
4	4/12/2021	14-2; 14-14
5	4/13/2021	14-2; 14-14
6	4/14/2021	14-2; 14-14
7	4/15/2021	14-2; 14-14
8	4/16/2021	14-2; 14-14
9	4/17/2021	14-2; 14-14
10	4/18/2021	14-2; 14-14
11	4/19/2021	14-2; 14-14
12	4/20/2021	14-2; 14-14
13	4/21/2021	14-2; 14-14
14	4/22/2021	14-2; 14-14
15	4/23/2021	14-2; 14-14
Sheet3

Cell Formulas
Range	Formula
A2:A1558	A2	=SEQUENCE(MAX(Sheet2!B2:V24)-MIN(Sheet2!B2:V24)+1,,MIN(Sheet2!B2:V24))
B2:B15	B2	=TEXTJOIN("; ",1,IF((A2>=Sheet2!$B$2:$V$24)*(A2<Sheet2!$B$3:$V$25),Sheet2!$B$1:$V$1&"-"&Sheet2!$A$2:$A$24,""))
Dynamic array formulas.

and at the end:

Book1
	A	B
1544	6/30/2025	34-22; 33-23
1545	7/1/2025	34-22; 33-23
1546	7/2/2025	34-22; 33-23
1547	7/3/2025	34-22; 33-23
1548	7/4/2025	34-22; 33-23
1549	7/5/2025	34-22; 33-23
1550	7/6/2025	34-22; 33-23
1551	7/7/2025	34-22; 33-23
1552	7/8/2025	34-22; 33-23
1553	7/9/2025	34-22; 33-23
1554	7/10/2025	34-22; 33-23
1555	7/11/2025	34-22; 33-23
1556	7/12/2025	34-22
1557	7/13/2025	34-22
1558	7/14/2025	34-23
1559
Sheet3

Cell Formulas
Range	Formula
B1544:B1559	B1544	=TEXTJOIN("; ",1,IF((A1544>=Sheet2!$B$2:$V$24)*(A1544<Sheet2!$B$3:$V$25),Sheet2!$B$1:$V$1&"-"&Sheet2!$A$2:$A$24,""))

 

[joercav]

Again, thanks for your help, but I'm not able to duplicate your same results. Not sure why, I'm getting blanks...

 

[joercav]

Many thanks. I just learned a whole bunch from you. To make it work I modified the tables and your formula. I added "end dates" below the original start dates and used this formula =TEXTJOIN(";",TRUE,IF($A787>='Cycle Schedule'!$S$66:$CE$93,IF($A787<'Cycle Schedule'!$S$96:$CE$123,'Cycle Schedule'!$S$1:$CE$1&"-"&'Cycle Schedule'!$B$96:$B$123,""),""))