RANDOMDATE

[lrobbo314]

RANDOMDATE will return a number of random dates given a range of years.

If you omit the optional third argument, the function will return a single random date.

Inspired by a recent video by @MrExcel, video link.

  RANDOMDATE
  =LAMBDA(
    year_start,year_end,[num_dates],
        LET(
            s,SEQUENCE(num_dates),
                MAP(s,LAMBDA(x,DATE(RANDBETWEEN(year_start,year_end),1,RANDBETWEEN(1,365))))
        )
)

RANDOMDATE
	A	B	C	D	E
1	With all arguments entered				Without optional argument
2	2/28/2013				6/19/2019
3	6/24/2009
4	6/20/2022
5	8/25/2022
6	1/12/2015
7	12/27/2020
8	5/4/2019
9	9/7/2017
10	2/5/2022
11	12/26/2011
Sheet2

Cell Formulas
Range	Formula
A2:A11	A2	=RANDOMDATE(2008,2022,10)
E2	E2	=RANDOMDATE(2008,2022,)
Dynamic array formulas.

I tried to adapt a VBA function to a LAMBDA function, but kept getting a "Nested Arrays" error whenever I tried to incorporate the MAP function in order to be able to return multiple dates.

Here is the function I was trying to use.

=LAMBDA(
    year_start,year_end,
        LET(
            m,RANDBETWEEN(1,12),
            y,RANDBETWEEN(year_start,year_end),
            d,
                IF(OR(m={1,3,5,7,8,10,12}),31,
                IF(OR(m={4,6,9,11}),30,
                    IF(MOD(y,4)=0,
                        29,
                        28
                    )
                )
                ),
                DATE(y,m,d)
        )
)

I would be interested to know if anyone would be able to tell me how you could make that one work.

But, @MrExcel's version was more elegant anyway.

 

[MrExcel]

Thanks for this awesome Lambda. I will add a note to the video description sending people to this formula.

 

[smozgur]

Another version using the "counting days" logic from the start and MAKEARRAY function:

=LAMBDA(start_year,end_year,[how_many],
    LET(
        how_many, IF(how_many <=0, 1, how_many),
        total_days,DATE(end_year,12,31) - DATE(start_year,1,1),
        MAKEARRAY(how_many, 1,
            LAMBDA(r,c,
                LET(random_days,RANDBETWEEN(0,total_days),
                    result,TEXT(DATE(start_year,1,random_days), "mm/dd/yyyy"),
                    result
                )
          )
      ))
)

Book10
	A	B	C	D
1	Start Year	2005		Random Days
2	End Year	2006		05/14/2006
3	How Many	10		01/10/2005
4				09/03/2006
5				12/26/2006
6				08/31/2005
7				12/27/2005
8				05/22/2006
9				04/01/2006
10				10/27/2006
11				10/11/2005
Sheet1

Cell Formulas
Range	Formula
D2:D11	D2	=LAMBDA(start_year,end_year,[how_many], LET( how_many, IF(how_many <=0, 1, how_many), total_days,DATE(end_year,12,31)-DATE(start_year,1,1), MAKEARRAY(how_many, 1, LAMBDA(r,c, LET(random_days,RANDBETWEEN(0,total_days), result,TEXT(DATE(start_year,1,random_days), "mm/dd/yyyy"), result ) ) )) )(B1,B2,B3)
Dynamic array formulas.

 

[Xlambda]

Great challenge, great functions both!!
My take:
RANDATE(ya,[yb],[r],[c],[fm])
ya: year 1
[yb]: year 2, if omitted => year 1 (no error is returned if ya>yb)
[r]: nr. rows to spill
[c]: nr. clms to spill
- if [r] omitted => r=1, functions spills row vector of "c" values
- if [c] omitted => c=1, function spills clm vector of "r" values
- if both omitted => 1 value only
- if none omitted => function spills 2D array "r" rows x "c" columns
[fm]: format argument, if omitted, no format, if not omitted => TEXT(values,fm)

=LAMBDA(ya, [yb], [r], [c], [fm],
    LET(
        y, IF(yb, yb, ya),
        x, RANDARRAY(IF(r, r, 1), IF(c, c, 1), "1-1-" & MIN(ya, y), "31-12-" & MAX(ya, y), 1),
        IF(fm = "", x, TEXT(x, fm))
    )
)

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1
2		no arguments omitted				single date				numeric dates, fm, omitted
3		=RANDATE(2022,2020,5,3,"ddd dd mmm-yyy")				=RANDATE(2022,,,,"ddd mmm-yyy")				=RANDATE(2000,2022,27)
4		Fri 07 Jan-2022	Sun 29 May-2022	Tue 18 May-2021		Sun Aug-2022				37769
5		Wed 02 Feb-2022	Wed 12 May-2021	Thu 17 Sep-2020						41148
6		Tue 25 Aug-2020	Wed 14 Dec-2022	Fri 17 Dec-2021		=RANDATE(2022)				42224
7		Wed 28 Sep-2022	Sat 22 Aug-2020	Mon 03 Aug-2020		44689				40585
8		Sat 09 Oct-2021	Tue 23 Aug-2022	Sat 30 Apr-2022						38795
9										43234
10		row spill, r,omitted								37553
11		=RANDATE(2022,2018,,7,"yyy-mmm dd (ddd)")								40229
12		2022-Jul 07 (Thu)	2021-Apr 12 (Mon)	2019-Jul 26 (Fri)	2018-Oct 23 (Tue)	2018-Aug 11 (Sat)	2022-Mar 18 (Fri)	2019-Jun 09 (Sun)		43886
13										39519
14		clm spill, c, omitted		all dates same year, yb, omitted						39295
15	=RANDATE(2022,2015,15,,"mm/dd/yyy ddd")			=RANDATE(2022,,15,5,"ddd dd/mmm yy")						44287
16		08/16/2016 Tue		Fri 11/Feb 22	Sat 29/Jan 22	Fri 14/Oct 22	Thu 17/Feb 22	Thu 24/Mar 22		38208
17		12/06/2015 Sun		Fri 13/May 22	Wed 14/Sep 22	Mon 10/Jan 22	Thu 27/Oct 22	Fri 25/Nov 22		38227
18		08/17/2020 Mon		Tue 09/Aug 22	Sat 20/Aug 22	Mon 22/Aug 22	Sat 30/Jul 22	Mon 16/May 22		36681
19		03/17/2016 Thu		Sun 27/Nov 22	Wed 23/Mar 22	Sun 27/Feb 22	Sat 05/Mar 22	Wed 14/Sep 22		41502
20		08/09/2020 Sun		Wed 11/May 22	Mon 20/Jun 22	Thu 01/Sep 22	Tue 26/Jul 22	Wed 25/May 22		39945
21		05/21/2020 Thu		Fri 25/Mar 22	Tue 08/Nov 22	Thu 08/Dec 22	Sat 15/Jan 22	Fri 06/May 22		42286
22		08/07/2018 Tue		Sun 24/Apr 22	Mon 04/Jul 22	Sun 29/May 22	Tue 25/Jan 22	Fri 16/Dec 22		38259
23		01/15/2022 Sat		Mon 21/Nov 22	Tue 01/Nov 22	Mon 21/Nov 22	Sun 13/Mar 22	Wed 11/May 22		36766
24		07/06/2016 Wed		Tue 04/Jan 22	Fri 16/Sep 22	Fri 28/Oct 22	Sat 01/Jan 22	Sat 01/Jan 22		40011
25		05/28/2022 Sat		Sat 11/Jun 22	Sat 12/Mar 22	Mon 12/Sep 22	Sat 29/Jan 22	Wed 09/Mar 22		44847
26		06/17/2020 Wed		Thu 03/Mar 22	Sat 22/Oct 22	Thu 22/Sep 22	Wed 24/Aug 22	Sat 16/Apr 22		37291
27		03/25/2019 Mon		Sun 16/Oct 22	Sat 22/Oct 22	Sun 29/May 22	Thu 11/Aug 22	Wed 23/Nov 22		38901
28		06/05/2018 Tue		Mon 30/May 22	Tue 31/May 22	Thu 03/Nov 22	Tue 03/May 22	Mon 25/Jul 22		36656
29		09/02/2016 Fri		Sun 26/Jun 22	Sat 05/Mar 22	Wed 11/May 22	Tue 23/Aug 22	Thu 04/Aug 22		39354
30		07/24/2016 Sun		Thu 17/Feb 22	Mon 21/Feb 22	Thu 24/Mar 22	Fri 04/Nov 22	Fri 22/Apr 22		44305
31
32
Sheet2

Cell Formulas
Range	Formula
B3,D15,B11,F6,J3,F3	B3	=FORMULATEXT(B4)
B4:D8	B4	=RANDATE(2022,2020,5,3,"ddd dd mmm-yyy")
F4	F4	=RANDATE(2022,,,,"ddd mmm-yyy")
J4:J30	J4	=RANDATE(2000,2022,27)
F7	F7	=RANDATE(2022)
B12:H12	B12	=RANDATE(2022,2018,,7,"yyy-mmm dd (ddd)")
A15	A15	=FORMULATEXT(B16)
B16:B30	B16	=RANDATE(2022,2015,15,,"mm/dd/yyy ddd")
D16:H30	D16	=RANDATE(2022,,15,5,"ddd dd/mmm yy")
Dynamic array formulas.

 

[smozgur]

I knew that I could use RANDARRAY instead of using an additional array function!!!

Nice one, @Xlambda! Thanks.

It is wonderful to see different usages of various array functions. That's why I love the Lambda forum.

 

[Xlambda]

What if we need distributions of dates that need to have no duplicates (all dates delivered should be unique)?
UNQRANDATE(ya,[yb],[r],[c],[fm],[k]) recursive function, calls RANDATE.
-same arguments like RANDATE , plus a randarray size incrementor argument [k]
[k]: always omitted, with every iteration k(i+1)=k(i)*5 , k(0)=r*c ; until rows(unique(values))=r*c

=LAMBDA(ya, [yb], [r], [c], [fm], [k],
    LET(
        w, MAX(1, r),
        l, MAX(1, c),
        n, w * l,
        i, IF(k, k, n),
        a, RANDATE(ya, yb, i, , fm),
        u, UNIQUE(a),
        o, ROWS(u),
        IF(o >= n, INDEX(u, SEQUENCE(w, l)), UNQRANDATE(ya, yb, w, l, fm, i * 5))
    )
)

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		Task: Random array distribution of dates, year 2022 , 36 rows x 10 columns, no duplicates
2
3		=UNQRANDATE(2022,,36,10,"dd-mm")
4		02-06	14-01	21-09	20-01	20-05	12-12	05-07	29-11	28-06	21-12		check unique
5		12-11	29-10	16-07	31-12	18-02	22-02	30-09	15-08	30-06	13-03		=ROWS(UNIQUE(TOCOL(B4#)))
6		07-03	10-08	06-08	22-06	08-07	27-12	26-08	10-09	19-09	03-09		360
7		29-06	07-02	24-09	08-08	20-09	25-11	19-05	29-01	07-01	14-07
8		08-06	02-03	26-04	15-02	13-09	30-04	23-01	11-10	04-12	15-12		=ROWS(B4#)*COLUMNS(B4#)
9		10-05	25-09	06-04	03-12	08-10	26-09	18-05	14-02	01-02	07-10		360
10		13-05	06-01	05-01	25-12	26-03	27-03	14-03	14-11	02-01	28-03
11		13-06	06-02	05-02	14-10	23-11	16-05	04-08	01-10	11-04	11-07
12		02-04	11-06	18-01	24-05	01-07	20-03	01-05	31-08	08-02	18-10
13		18-07	11-03	23-04	18-11	24-01	08-01	04-09	16-12	06-06	07-07
14		20-07	21-03	28-09	07-11	09-11	06-12	25-10	21-08	20-06	25-02
15		30-05	05-09	23-02	12-01	29-08	15-11	19-01	05-12	05-06	04-06
16		03-02	23-12	27-05	17-04	07-04	15-10	05-05	24-08	05-11	18-03
17		24-10	18-08	19-06	04-03	19-02	31-05	09-09	14-05	19-10	27-08
18		06-05	26-10	02-09	12-06	10-01	20-02	26-07	19-12	24-07	11-01
19		22-10	10-10	16-03	22-09	29-09	15-04	09-03	23-10	28-07	21-02
20		01-09	24-11	05-08	08-11	02-11	11-02	30-01	04-07	17-02	27-10
21		12-04	17-09	23-09	26-02	17-03	02-10	24-04	02-05	22-01	03-01
22		30-10	22-08	06-03	10-04	09-12	06-10	07-12	11-08	10-07	09-05
23		19-04	17-08	17-10	04-01	25-08	07-06	25-03	05-03	02-02	10-03
24		07-09	01-04	28-04	29-03	16-02	31-03	16-04	10-02	03-07	20-11
25		21-06	15-05	25-01	15-01	23-03	30-08	12-03	09-07	20-08	10-11
26		22-04	17-11	02-12	04-11	13-10	12-08	26-01	20-12	18-06	01-11
27		23-08	26-06	13-07	27-11	22-03	31-10	17-07	24-12	21-01	11-12
28		09-10	25-06	19-07	28-02	29-12	20-10	18-09	28-10	28-08	12-09
29		16-08	28-11	01-08	13-01	06-09	30-03	10-12	24-06	04-10	21-10
30		22-07	13-02	16-11	14-09	07-08	09-08	05-10	20-04	22-12	03-05
31		04-02	19-03	09-02	09-06	29-04	15-03	19-08	02-08	08-03	25-04
32		13-11	26-05	03-10	08-09	27-01	12-07	18-12	13-12	30-11	19-11
33		01-12	30-12	03-04	23-05	27-09	03-06	17-05	14-12	27-02	08-05
34		28-05	31-01	04-05	11-09	04-04	16-09	09-04	08-04	11-05	06-11
35		30-07	26-11	25-05	17-06	08-12	01-01	17-01	14-08	12-05	21-07
36		05-04	27-06	24-02	23-06	26-12	23-07	16-10	31-07	01-06	15-06
37		16-06	28-12	27-04	01-03	21-05	15-09	14-06	21-11	06-07	27-07
38		24-03	17-12	29-07	11-11	13-04	12-10	25-07	09-01	12-02	14-04
39		29-05	07-05	13-08	16-01	03-11	03-08	18-04	22-05	15-07	21-04
40
Sheet3

Cell Formulas
Range	Formula
B3,M8,M5	B3	=FORMULATEXT(B4)
B4:K39	B4	=UNQRANDATE(2022,,36,10,"dd-mm")
M6	M6	=ROWS(UNIQUE(TOCOL(B4#)))
M9	M9	=ROWS(B4#)*COLUMNS(B4#)
Dynamic array formulas.

 

[Xlambda]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		If nr. of dates we need (dates with no dups) is greater than total days between the 2 years => impossible => error
2		=> nr. of unq dates we need (dates with no dups) should be <= total days btwn the years interval.
3		r*c <= (31-Dec-max(ya,yb))-(1-Jan-min(ya,yb))+1
4		Example for years interval 2020-2021
5		2020 (leap year) had 366 days and 2021 had 365 days => max possible days=731
6		732 days >731 => error			731 days => all unq (no dups)					60x4=240 unq dates (no dups)
7		=UNQRANDATE(2021,2020,732)			=UNQRANDATE(2021,2020,731)					=UNQRANDATE(2020,2021,60,4,"dd-mmm-yy")
8		#VALUE!			43902					01-Aug-20	11-Oct-20	16-Feb-21	25-Dec-20
9					43856		check unique			10-Feb-21	28-Apr-20	16-Oct-20	30-Nov-20
10					44351		=ROWS(UNIQUE(E8#))			24-Jun-21	26-Feb-21	08-Sep-21	16-Aug-20
11					44548		731			18-Dec-21	30-Oct-21	21-Mar-21	28-May-20
12					44149					28-Sep-21	06-Jul-20	18-Nov-20	09-Dec-20
13					43945		=ROWS(E8#)			09-Apr-20	09-Apr-21	04-Dec-21	02-Jun-20
14					44379		731			07-Feb-20	14-Apr-20	30-May-21	12-Jun-21
15					44538					23-Mar-20	31-Jul-21	04-Apr-21	24-Nov-20
16					43855		=ROWS(UNIQUE(TOCOL(J8#)))			08-Sep-20	18-Mar-20	31-Jan-21	26-Oct-20
17					44260		240			16-Nov-21	19-Apr-20	08-Jul-20	14-Jan-21
18					44193					25-Feb-20	07-Jan-20	21-Mar-20	03-Oct-20
19					44389		=COUNTA(J8#)			01-Jul-21	17-Aug-21	19-Feb-21	18-Sep-21
20					44128		240			21-May-20	12-Jan-21	21-Dec-20	23-Jun-21
21					44441					26-Jul-21	15-Dec-21	25-Sep-21	07-Sep-21
22					44006					02-Apr-21	22-Nov-21	18-Jan-21	20-Apr-21
23					44082					24-Apr-20	12-Dec-20	02-Mar-21	02-Aug-20
24					44367					05-Jan-21	21-Sep-21	12-Jul-21	04-Feb-20
25					44051					28-Mar-20	10-Dec-21	05-Oct-21	14-Jul-20
26					44197					22-Oct-21	08-Feb-20	19-May-21	01-Aug-21
27					43872					07-Jul-20	03-Mar-21	23-Apr-20	02-Sep-21
28					44243					29-Nov-21	24-Dec-21	20-Feb-21	28-Dec-21
29					44244					21-Jul-21	24-Jul-21	03-Jun-20	11-Nov-21
30					44504					06-Apr-20	06-Jan-20	06-Jul-21	10-Jul-21
31					44013					05-Apr-20	24-Aug-21	22-Nov-20	26-Apr-20
32					43848					08-Dec-21	15-Sep-20	18-Dec-20	03-Nov-21
33					44198					09-Jul-21	12-Jun-20	02-Aug-21	30-Mar-21
34					44223					14-Oct-20	12-Feb-21	24-Apr-21	07-Jun-20
35					44248					12-Nov-21	17-Dec-21	22-Apr-20	21-Nov-21
36					44420					21-Jun-21	28-Nov-21	11-Oct-21	17-Mar-21
37					44540					27-Nov-21	11-Jun-21	15-Apr-21	16-Nov-20
38					43983					13-Dec-20	25-Jan-20	27-Feb-21	06-Nov-21
39					44348					30-Dec-21	11-Nov-20	23-Sep-20	31-Mar-21
40					43940					02-Jul-20	24-Feb-20	23-Dec-21	28-Jun-21
41					43895					14-Apr-21	22-Jun-21	02-May-21	22-Jun-20
42					44324					17-Jun-21	19-Nov-21	16-Jun-21	25-Jun-20
43					43854					08-Jan-20	12-Aug-21	05-Nov-21	22-May-21
44					44132					05-Feb-21	28-Jan-21	29-May-21	26-Jun-21
45					44158					03-Dec-21	30-Aug-20	09-Sep-21	24-Jul-20
46					43886					28-Mar-21	16-May-21	29-Jun-20	14-Aug-20
47					44495					27-Aug-20	31-Dec-20	26-May-20	23-May-20
48					44386					27-Apr-20	05-Jun-20	17-Nov-21	22-Dec-21
49					44529					17-Oct-20	27-Nov-20	09-Mar-20	25-Oct-20
50					44272					30-Apr-20	03-Oct-21	03-May-21	02-Jan-21
51					44212					03-Feb-21	11-Aug-20	08-May-20	08-Nov-21
52					44068					19-Nov-20	14-Sep-21	19-Sep-21	02-Nov-21
53					44092					13-Jun-21	10-Mar-21	27-May-21	11-Feb-21
54					44299					26-Jul-20	15-Nov-21	11-Mar-21	16-Sep-20
55					43914					10-Sep-20	07-Feb-21	29-Nov-20	18-Apr-21
56					44328					22-Oct-20	17-Nov-20	20-Nov-20	14-Oct-21
57					44267					06-Jun-21	20-May-20	04-Jul-21	26-Aug-20
58					44308					04-Jun-21	10-Aug-21	14-Jun-20	02-Oct-21
59					43831					09-Mar-21	15-Jan-20	14-Sep-20	09-Nov-20
60					44423					09-Oct-20	17-Apr-21	08-Mar-21	26-Dec-21
61					44181					11-Aug-21	04-Apr-20	10-Apr-21	19-Jun-21
62					44205					25-Jul-20	15-Feb-20	28-Jun-20	27-Jun-21
63					44499					01-Nov-20	20-Dec-20	06-Apr-21	03-Jun-21
64					44539					23-Apr-21	09-Jun-20	13-Mar-20	27-Jun-20
65					44511					27-Oct-20	29-Oct-20	02-Jul-21	04-Nov-20
66					44161					25-Mar-21	05-Jun-21	10-Mar-20	16-Aug-21
67					44557					09-Aug-21	30-Aug-21	05-Mar-20	17-Oct-21
68					43866
69					44061
Sheet6

Cell Formulas
Range	Formula
B7,E7,G19,G16,G13,G10,J7	B7	=FORMULATEXT(B8)
B8	B8	=UNQRANDATE(2021,2020,732)
E8:E738	E8	=UNQRANDATE(2021,2020,731)
J8:M67	J8	=UNQRANDATE(2020,2021,60,4,"dd-mmm-yy")
G11	G11	=ROWS(UNIQUE(E8#))
G14	G14	=ROWS(E8#)
G17	G17	=ROWS(UNIQUE(TOCOL(J8#)))
G20	G20	=COUNTA(J8#)
Dynamic array formulas.

 

[Xlambda]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Leap year 2020 has 366 days=> all days can fit a 61x6 array (61*6=366)
2
3		=UNQRANDATE(2020,,61,6,"dd-mm")
4		06-11	21-07	06-08	28-12	22-08	01-01		check unique
5		23-07	26-08	29-08	24-12	02-05	12-11		=ROWS(UNIQUE(TOCOL(B4#)))
6		05-10	18-08	21-06	24-08	12-01	26-04		366
7		29-01	05-06	07-08	14-07	06-12	16-07
8		12-12	16-08	02-02	03-03	08-01	09-01		=COUNTA(B4#)
9		12-02	01-12	26-07	02-01	28-10	19-05		366
10		25-08	09-12	27-11	15-12	18-09	05-05
11		25-11	11-04	14-12	25-06	02-11	13-04			CF 29 Feb
12		15-08	17-03	03-05	15-11	07-10	08-04
13		11-05	19-06	27-12	01-09	01-02	04-06
14		12-08	10-02	10-07	16-05	13-01	08-02		Obviously, trying to return 367 unique days, for same year
15		19-09	02-07	11-03	11-07	22-10	16-04		can never work, function will run out of max iterations limit
16		12-03	16-02	01-03	30-01	09-03	23-01		=UNQRANDATE(2020,,367)
17		24-03	10-03	05-11	28-09	24-05	29-06		#VALUE!
18		20-02	02-09	11-02	05-07	03-11	26-12
19		22-02	11-01	03-12	31-01	08-08	05-02
20		25-01	05-01	11-06	27-07	28-07	03-01
21		22-07	04-07	06-06	02-10	15-09	24-01
22		03-08	10-09	24-09	10-10	20-04	24-07
23		08-09	12-04	23-10	16-12	20-05	17-07
24		27-02	19-12	08-11	02-08	04-04	29-02
25		03-09	17-11	04-03	26-03	01-06	07-02
26		15-03	24-02	26-11	04-08	11-10	24-06
27		30-03	27-01	29-04	25-09	09-09	07-05
28		28-05	13-03	18-04	09-11	10-06	07-06
29		16-01	28-06	09-10	23-06	29-09	12-09
30		14-08	30-06	27-10	18-07	29-07	03-02
31		22-09	29-03	23-12	30-12	30-07	12-05
32		28-02	30-08	20-07	13-08	09-07	14-03
33		18-02	17-04	15-01	06-09	03-04	26-05
34		17-02	23-02	06-01	03-06	19-10	13-11
35		19-08	09-08	14-04	20-09	24-10	04-12
36		16-09	10-12	14-05	25-03	07-03	21-11
37		04-05	08-03	06-10	27-03	22-06	20-11
38		08-07	11-11	21-12	12-10	26-01	20-10
39		19-03	15-07	25-07	08-12	20-08	16-11
40		08-05	17-01	19-07	23-08	09-04	28-04
41		14-01	18-12	13-06	20-06	22-05	12-06
42		19-01	18-03	07-01	15-06	09-02	18-01
43		10-01	10-11	23-04	15-04	23-09	27-08
44		19-04	25-05	04-01	10-08	21-08	06-05
45		17-05	22-12	03-10	06-07	21-01	10-05
46		23-11	06-04	07-12	14-10	01-08	07-11
47		15-10	20-03	31-12	16-03	31-03	14-06
48		18-06	22-01	17-09	04-09	04-11	31-10
49		13-12	07-07	17-08	11-08	10-04	30-11
50		28-08	05-12	02-03	13-10	21-09	15-02
51		03-07	04-10	20-01	28-11	07-04	05-03
52		22-11	27-06	21-04	05-04	25-12	07-09
53		21-02	14-09	22-03	30-04	30-05	04-02
54		05-08	23-03	18-11	29-12	28-01	08-10
55		15-05	23-05	21-03	19-02	16-10	13-09
56		24-04	26-10	12-07	08-06	26-06	29-11
57		22-04	21-10	27-09	25-04	31-05	29-05
58		01-04	25-02	21-05	30-10	17-12	25-10
59		27-04	17-10	13-02	05-09	13-07	02-04
60		24-11	01-10	09-06	18-10	09-05	30-09
61		13-05	16-06	11-09	26-09	01-05	18-05
62		29-10	06-03	27-05	28-03	14-02	14-11
63		20-12	01-11	19-11	31-08	11-12	17-06
64		31-07	02-12	06-02	26-02	02-06	01-07
65
Sheet4

Cell Formulas
Range	Formula
B3,I16,I8,I5	B3	=FORMULATEXT(B4)
B4:G64	B4	=UNQRANDATE(2020,,61,6,"dd-mm")
I6	I6	=ROWS(UNIQUE(TOCOL(B4#)))
I9	I9	=COUNTA(B4#)
I17	I17	=UNQRANDATE(2020,,367)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4:G64	Expression	=B4="29-02"	text	NO

 

[jaeiow]

RANDOMDATE will return a number of random dates given a range of years.

If you omit the optional third argument, the function will return a single random date.

Inspired by a recent video by @MrExcel, video link.

  RANDOMDATE
  =LAMBDA(
    year_start,year_end,[num_dates],
        LET(
            s,SEQUENCE(num_dates),
                MAP(s,LAMBDA(x,DATE(RANDBETWEEN(year_start,year_end),1,RANDBETWEEN(1,365))))
        )
)

Needed to remove the spaces from before the = before it took.

RANDOMDATE I'm getting a Value error with this one, @Xlambda. I copied it into the name manager as I usually do, but doesn't seem to be correct as written here.

 

[Xlambda]

RANDOMDATE I'm getting a Value error with this one, @Xlambda. I copied it into the name manager as I usually do, but doesn't seem to be correct as written here.

Function is fine!! I opened a new workbook, copied the function, function works. See the "cell formulas" section beneath mini-sheet? Formulas are identical to the initial post. Double check your data, arguments...
You should post a mini-sheet example, do the same, like I did with this one: (lambda directly in cell, not as function defined name)

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2
3						=FORMULATEXT(B4)
4		44184	44644	43988		=LAMBDA(ya,[yb],[r],[c],[fm], LET( y, IF(yb, yb, ya), x, RANDARRAY(IF(r, r, 1), IF(c, c, 1), "1-1-" & MIN(ya, y), "31-12-" & MAX(ya, y), 1), IF(fm = "", x, TEXT(x, fm)) ) )(2022,2020,5,3)
5		44357	44185	44464
6		44409	44341	43881
7		44226	44570	44865
8		44126	44259	44522
9
10
11
12						=FORMULATEXT(B13)
13		44345				=LAMBDA(ya,[yb],[r],[c],[fm], LET( y, IF(yb, yb, ya), x, RANDARRAY(IF(r, r, 1), IF(c, c, 1), "1-1-" & MIN(ya, y), "31-12-" & MAX(ya, y), 1), IF(fm = "", x, TEXT(x, fm)) ) )(2022,2015,15)
14		43847
15		43413
16		42024
17		42465
18		42869
19		42164
20		42882
21		44852
22		42385
23		42366
24		44281
25		43130
26		43316
27		43468
28
Sheet1

Cell Formulas
Range	Formula
F3,F12	F3	=FORMULATEXT(F4)
B4:D8	B4	=LAMBDA(ya,[yb],[r],[c],[fm], LET( y, IF(yb, yb, ya), x, RANDARRAY(IF(r, r, 1), IF(c, c, 1), "1-1-" & MIN(ya, y), "31-12-" & MAX(ya, y), 1), IF(fm = "", x, TEXT(x, fm)) ) )(2022,2020,5,3)
F4,F13	F4	=FORMULATEXT(B4)
B13:B27	B13	=LAMBDA(ya,[yb],[r],[c],[fm], LET( y, IF(yb, yb, ya), x, RANDARRAY(IF(r, r, 1), IF(c, c, 1), "1-1-" & MIN(ya, y), "31-12-" & MAX(ya, y), 1), IF(fm = "", x, TEXT(x, fm)) ) )(2022,2015,15)
Dynamic array formulas.