RANDOMDATE

RANDOMDATE(start_year, end_year, [num_dates])
start_year
the lower limit of the range of years for the random date.
end_year
the upper limit of the range of years for the random date.
[num_dates]
the number of dates to be returned.
RANDOMDATE will return a number of random dates given a range of years.
lrobbo314

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,963
Office Version
  1. 365
Platform
  1. Windows
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.

Excel Formula: 
  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
ABCDE
1With all arguments enteredWithout optional argument
22/28/20136/19/2019
36/24/2009
46/20/2022
58/25/2022
61/12/2015
712/27/2020
85/4/2019
99/7/2017
102/5/2022
1112/26/2011
Sheet2
Cell Formulas
RangeFormula
A2:A11A2=RANDOMDATE(2008,2022,10)
E2E2=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.

Excel Formula: 
=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.
 
Upvote 0
Ok. I see that it has to match up with the regional date format. 12-31 for US.
Excel Formula: 
=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), ""12-31-"" & MAX(ya, y), 1),
        IF(fm = """", x, TEXT(x, fm))
    )
)(2022,2020,5,3)
 
lrobbo314 said:
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.

Excel Formula: 
  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
ABCDE
1With all arguments enteredWithout optional argument
22/28/20136/19/2019
36/24/2009
46/20/2022
58/25/2022
61/12/2015
712/27/2020
85/4/2019
99/7/2017
102/5/2022
1112/26/2011
Sheet2
Cell Formulas
RangeFormula
A2:A11A2=RANDOMDATE(2008,2022,10)
E2E2=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.

Excel Formula: 
=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.
Click to expand...
What I found interesting here is that the optional num_dates parameter has no ISOMITTED, and the SEQUENCE function does not generate an error in spite of it seemingly not getting any required value.
In fact, as noted SEQUENCE returns 1 when the parameter is not provided, for example
Excel Formula: 
=LAMBDA([Optional], SEQUENCE(Optional))()
returns 1, but
Excel Formula: 
=SEQUENCE(0)
returns a #CALC! error! Yet
Excel Formula: 
=LAMBDA([Optional], Optional)()
returns 0.
This is quite a find, but I don't understand it! Can anyone explain it? Great discovery for me none the less!
 
jdellasala said:
What I found interesting here is that the optional num_dates parameter has no ISOMITTED, and the SEQUENCE function does not generate an error in spite of it seemingly not getting any required value.
In fact, as noted SEQUENCE returns 1 when the parameter is not provided, for example
Excel Formula: 
=LAMBDA([Optional], SEQUENCE(Optional))()
returns 1, but
Excel Formula: 
=SEQUENCE(0)
returns a #CALC! error! Yet
Excel Formula: 
=LAMBDA([Optional], Optional)()
returns 0.
This is quite a find, but I don't understand it! Can anyone explain it? Great discovery for me none the less!
Click to expand...
Is quite simple:
=SEQUENCE(,) returns 1
Any row/column argument in any function is designed to return 1 when that argument is omitted.
When you call SEQUENCE(0) the argument is not omitted, is 0 and 0 rows don't work, when omitted, value 1 is assigned.
 
smozgur said:
Another version using the "counting days" logic from the start and MAKEARRAY function:

Excel Formula: 
=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
ABCD
1Start Year2005Random Days
2End Year200605/14/2006
3How Many1001/10/2005
409/03/2006
512/26/2006
608/31/2005
712/27/2005
805/22/2006
904/01/2006
1010/27/2006
1110/11/2005
Sheet1
Cell Formulas
RangeFormula
D2:D11D2=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.
Click to expand...
Suat,
Love the LAMBDA, but noticed one TINY error.
Excel Formula: 
DATE(end_year,12,31) - DATE(start_year,1,1)
equals only 364 days for a non leap year, and 365 for only a leap year. To get 365 for a non leap year, it would have to be
Excel Formula: 
DATE(end_year,12,31) - DATE(start_year,1,1)+1
.
I only noticed it because it bites me all the time!
One could argue that it's the number of days between the end of the first day and the last day. Still a nice LAMBDA!
 
jdellasala said:
Suat,
Love the LAMBDA, but noticed one TINY error.
Excel Formula: 
DATE(end_year,12,31) - DATE(start_year,1,1)
equals only 364 days for a non leap year, and 365 for only a leap year. To get 365 for a non leap year, it would have to be
Excel Formula: 
DATE(end_year,12,31) - DATE(start_year,1,1)+1
.
I only noticed it because it bites me all the time!
One could argue that it's the number of days between the end of the first day and the last day. Still a nice LAMBDA!
Click to expand...
Nice catch, Jerry. Thank you!

I actually meant to use DATE(end_year,12,31) - DATE(start_year,1,0) as it also bits me all the time (because I always forget to include the starting item - in fact, I built my final steel bridge project with missing girders just because of this at the university. Glad I am not building bridges in the real life!), but I did it again, and it looks like I only have 364 days in a year!

I changed the day parameter of the function, so it should work without problems now. Thanks again.
 
smozgur said:
Nice catch, Jerry. Thank you!

I actually meant to use DATE(end_year,12,31) - DATE(start_year,1,0) as it also bits me all the time (because I always forget to include the starting item - in fact, I built my final steel bridge project with missing girders just because of this at the university. Glad I am not building bridges in the real life!), but I did it again, and it looks like I only have 364 days in a year!

I changed the day parameter of the function, so it should work without problems now. Thanks again.
Click to expand...
LOL! Wait a second. The initial formula was correct because it was using RANDBETWEEN(0,total_days). So, It is 0 + 364, and if zero then it is the first day of the starting year.

I just changed it back :)
 
You must log in or register to reply here.

Forum statistics

Threads
1,224,836
Messages
6,181,251
Members
453,027
Latest member
Lost_in_spreadsheets

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top