RANDARRAY Date Formula Issues

[WhatTheF]

I'm truly grateful for all the help I've received here over the past few weeks. I feel like I'm taking advantage of you, but I'm not sure where else to turn! Hopefully I'll start to get this all figured out, soon.

Until then, however, another question...

I'm working with two (2) Random Date formulas. One is setting an opening date, and the second is setting a random closing date. The opening date formula is working perfectly fine. However, the closing date doesn't seem to be using the opening date as the parameter, like I am intending. I'm sure I've just got the formula screwed up. Here are the parameters:

A1 - Number of project dates
D1 - Year of Project(s)
F2 - Quarter Project is supposed to be implemented.

All of the Close dates have to meet three parameters:
Cannot be before the Open date.
Cannot be before 15 March of the same year.
Cannot be after 15 October of the same year.

These are the MOST BASIC parameters. Eventually (or now, if the formula/code is simple enough) I will need it to be a random date anywhere from seven to 180 days after the open date. The problem is, once it starts to move out, it will still have to follow the same basic parameters as listed above, just under the next year... Meaning, If it Open on, say 31 October of 2025, it cannot Close on 02 February 2026. It will have to wait until at least 15 March 2026 to Close.

The CF is just there to highlight the discrepancies I'm looking to fix.

Here is, basically, what I'm working with:

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	11										2025			2
2
3					4/8/2025								6/25/2025
4					4/16/2025								5/26/2025
5					4/21/2025								4/30/2025
6					4/22/2025								4/18/2025
7					5/5/2025								4/19/2025
8					5/20/2025								5/11/2025
9					5/25/2025								6/18/2025
10					6/10/2025								9/2/2025
11					6/15/2025								9/26/2025
12					6/22/2025								6/27/2025
13					6/30/2025								7/4/2025
14
Sheet1

Cell Formulas
Range	Formula
E3:E13	E3	=IFS($N$1=1,SORT(RANDARRAY($A$1,1,DATE(K1,1,1),DATE(K1,3,31),TRUE)), $N$1=2,SORT(RANDARRAY($A$1,1,DATE(K1,4,1),DATE(K1,6,30),TRUE)), $N$1=3,SORT(RANDARRAY($A$1,1,DATE(K1,7,1),DATE(K1,9,30),TRUE)), $N$1=4,SORT(RANDARRAY($A$1,1,DATE(K1,10,1),DATE(K1,12,31),TRUE)))
M3:M13	M3	=IFS($N$1=1,(RANDARRAY($A$1,1,(E3),DATE($K$1,6,15),TRUE)), $N$1=2,(RANDARRAY($A$1,1,(E3),DATE($K$1,10,15),TRUE)), $N$1=3,(RANDARRAY($A$1,1,(E3),DATE($K$1,10,15),TRUE)), $N$1=4,(RANDARRAY($A$1,1,(E3),DATE($K$1,10,15),TRUE)))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M:M	Expression	=M3<E3	text	NO

 

[Eric W]

How about:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	11										2025			2
2
3					4/16/2025								8/19/2025
4					4/24/2025								6/17/2025
5					5/5/2025								9/3/2025
6					5/8/2025								9/6/2025
7					5/11/2025								8/17/2025
8					5/12/2025								8/21/2025
9					6/21/2025								9/26/2025
10					6/22/2025								10/15/2025
11					6/23/2025								9/4/2025
12					6/24/2025								9/15/2025
13					6/26/2025								10/10/2025
14
Sheet1

Cell Formulas
Range	Formula
E3:E13	E3	=IFS($N$1=1,SORT(RANDARRAY($A$1,1,DATE(K1,1,1),DATE(K1,3,31),TRUE)), $N$1=2,SORT(RANDARRAY($A$1,1,DATE(K1,4,1),DATE(K1,6,30),TRUE)), $N$1=3,SORT(RANDARRAY($A$1,1,DATE(K1,7,1),DATE(K1,9,30),TRUE)), $N$1=4,SORT(RANDARRAY($A$1,1,DATE(K1,10,1),DATE(K1,12,31),TRUE)))
M3:M13	M3	=BYROW(E3#,LAMBDA(s,LET(y,YEAR(s)+OR(AND(MONTH(s)=10,DAY(s)>15),MONTH(s)>10),RANDBETWEEN(MAX(s+7,DATE(y,3,15)),DATE(y,10,15)))))
Dynamic array formulas.

 

[WhatTheF]

How about:

Not sure what's different with yours and mine... I copied both from the formula bar at the bottom, and by clicking on the E3/M3, and each time, I get the same result:

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	11										2025			2
2
3					4/21/2025								#NAME?
4					5/1/2025
5					5/1/2025
6					5/8/2025
7					5/10/2025
8					6/2/2025
9					6/10/2025
10					6/10/2025
11					6/20/2025
12					6/23/2025
13					6/30/2025
14
Sheet1

Cell Formulas
Range	Formula
E3:E13	E3	=IFS($N$1=1,SORT(RANDARRAY($A$1,1,DATE(K1,1,1),DATE(K1,3,31),TRUE)), $N$1=2,SORT(RANDARRAY($A$1,1,DATE(K1,4,1),DATE(K1,6,30),TRUE)), $N$1=3,SORT(RANDARRAY($A$1,1,DATE(K1,7,1),DATE(K1,9,30),TRUE)), $N$1=4,SORT(RANDARRAY($A$1,1,DATE(K1,10,1),DATE(K1,12,31),TRUE)))
M3	M3	=BYROW(E3#,LAMBDA(s,LET(y,YEAR(s)+OR(AND(MONTH(s)=10,DAY(s)>15),MONTH(s)>10),RANDBETWEEN(MAX(s+7,DATE(y,3,15)),DATE(y,10,15)))))
Dynamic array formulas.

 

[Peter_SSs]

Not sure what's different with yours and mine...

Your Excel versions - your version does not have the BYROW function.
I have not checked all the details of what the formula is supposed to be doing but what happens if you amend Eric's formula in M3 to this?

=LET(s,E3#,LET(y,YEAR(s)+OR(AND(MONTH(s)=10,DAY(s)>15),MONTH(s)>10),RANDBETWEEN(MAX(s+7,DATE(y,3,15)),DATE(y,10,15))))

 

[Eric W]

My apologies! Peter has the right of it, Excel 2021 does not have BYROW. Sadly, Peter's version doesn't work right either, I tested it on a copy of 2021 I have on a laptop. The MAX works on the whole range, not just the row in question. You can try this version, but it requires that you drag down the formula:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	11										2025			2
2
3					4/22/2025								8/19/2025
4					4/25/2025								10/1/2025
5					4/30/2025								7/6/2025
6					5/7/2025								5/25/2025
7					5/12/2025								6/17/2025
8					6/4/2025								9/17/2025
9					6/8/2025								9/17/2025
10					6/10/2025								7/13/2025
11					6/10/2025								9/9/2025
12					6/15/2025								8/20/2025
13					11/12/2025								4/27/2026
14
Sheet1

Cell Formulas
Range	Formula
M3:M13	M3	=LET(s,E3,LET(y,YEAR(s)+OR(AND(MONTH(s)=10,DAY(s)>15),MONTH(s)>10),RANDBETWEEN(MAX(s+7,DATE(y,3,15)),DATE(y,10,15))))

 

[WhatTheF]

My apologies! Peter has the right of it, Excel 2021 does not have BYROW. Sadly, Peter's version doesn't work right either, I tested it on a copy of 2021 I have on a laptop. The MAX works on the whole range, not just the row in question. You can try this version, but it requires that you drag down the formula:

Oooof course it doesn't...! Seems like I'm always one step behind on things. I appreciate your help, but unfortunately, dragging-down isn't going to work, as this will have to be a Ph,D (Push here, Dummy) worksheet, with everything locked so that no one can screw things up without a little effort.

I've already had to re-install Excel on my primary computer once in the last seven days. I'm really hoping not to have to upgrade, now! I can't use the 365 versions, as my primary computer cannot be "connected" to any service-type systems. It's a company rule...

 

[Eric W]

Give this a try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	11										2025			4
2
3					10/24/2025								4/15/2026
4					10/26/2025								4/22/2026
5					11/2/2025								6/20/2026
6					11/10/2025								7/16/2026
7					11/22/2025								7/19/2026
8					11/23/2025								7/26/2026
9					11/23/2025								8/24/2026
10					12/17/2025								8/7/2026
11					12/20/2025								3/17/2026
12					12/22/2025								5/20/2026
13					12/29/2025								7/3/2026
14
Sheet1

Cell Formulas
Range	Formula
E3:E13	E3	=IFS($N$1=1,SORT(RANDARRAY($A$1,1,DATE(K1,1,1),DATE(K1,3,31),TRUE)), $N$1=2,SORT(RANDARRAY($A$1,1,DATE(K1,4,1),DATE(K1,6,30),TRUE)), $N$1=3,SORT(RANDARRAY($A$1,1,DATE(K1,7,1),DATE(K1,9,30),TRUE)), $N$1=4,SORT(RANDARRAY($A$1,1,DATE(K1,10,1),DATE(K1,12,31),TRUE)))
M3:M13	M3	=LET(s,E3#,minproj,7,LET(y,YEAR(s)+(s>DATE(YEAR(s),10,15-minproj)),RANDBETWEEN(IF(s+minproj>DATE(y,3,15),s+minproj,DATE(y,3,15)),DATE(y,10,15))))
Dynamic array formulas.

The minproj variable in the formula is the minimum number of days you want from the start of the project to the earliest end date possible.

 

[WhatTheF]

Give this a try:



The minproj variable in the formula is the minimum number of days you want from the start of the project to the earliest end date possible.

This is working really well. However, I'm running into an issue where it's actually moving things out TOO far! Is there any way we (like how I'm acting like I'm ANY part of the process other than making demands?!?) can make it so if the close date will fall past the current date ["TODAY()" I guess...?], the space is left blank?

(I'm sorry if this seems a little bit like it's missing the point. I'm also using this to go back to Q4 of last year because some of these projects are overlapping. In most cases, those projects already have Close dates assigned.)

 

[Eric W]

Well, this is what that would look like:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	11										2025			4
2
3					10/11/2025
4					10/15/2025
5					10/30/2025
6					11/1/2025
7					11/9/2025
8					11/11/2025
9					11/18/2025
10					11/19/2025
11					11/20/2025
12					11/27/2025
13					12/4/2025
14
Sheet6

Cell Formulas
Range	Formula
E3:E13	E3	=IFS($N$1=1,SORT(RANDARRAY($A$1,1,DATE(K1,1,1),DATE(K1,3,31),TRUE)), $N$1=2,SORT(RANDARRAY($A$1,1,DATE(K1,4,1),DATE(K1,6,30),TRUE)), $N$1=3,SORT(RANDARRAY($A$1,1,DATE(K1,7,1),DATE(K1,9,30),TRUE)), $N$1=4,SORT(RANDARRAY($A$1,1,DATE(K1,10,1),DATE(K1,12,31),TRUE)))
M3:M13	M3	=LET(s,E3#,minproj,7,y,YEAR(s)+(s>DATE(YEAR(s),10,15-minproj)),d,RANDBETWEEN(IF(s+minproj>DATE(y,3,15),s+minproj,DATE(y,3,15)),DATE(y,10,15)),IF(d<TODAY(),d,""))
Dynamic array formulas.

The dates for the fourth quarter, after 10/15, are trying to pick a range between 3/15 and 10/15 of the next year. Since today is 3/12, they all will be past the current date, so they all are blank. Is there some other condition you can use to decide how far out you want to go?