In this article I will show you how to generate random future Dates, Time &Datetime in excel using RAND(), RANDBETWEEN and WORKDAY.INTL functions.
RANDBETWEEN function returns a random integer in a range of integers.
= RANDBETWEEN(bottom, top) →→bottom(smallest integer) & top (largest integer)
And since excel stores date as sequential serial numbers, we can use RANDBETWEEN to generate a random serial number which will be formatted as a date.
=RANDBETWEEN(date1,date2)
To generate random Weekdays only use a combination of NETWORKDAYS and WORKDAY to filter out weekends as suggested by Richard Young on Linkedin;
=WORKDAY(startdate,RANDBETWEEN(0,NETWORKDAYS(startdate,enddate)-1))
On generating random Time, you need to understand first that excel stores time as a fractional portion of a 24 hour day.
For example 8:00:00 AM = 0.333333333333333 which is 8/24
And RAND() function returns random fractions between 0 and 1.
Therefore to generate random times use;
=start_time+RAND()
To generate random times between two dates;
=start_time + RAND()*(end_time - start_time)
To generate random DateTime between two Dates and two Time, Just add random time to the random date part;
= RANDBETWEEN(startdate, enddate) + (starttime + RAND()*(endtime - starttime))
NB:
RANDBETWEEN is a volatile function and recalculates evertime the worksheet is open.
To avoid this;
►Go to→File→Options→Formulas→Select Manual calculation→Untick Recalculate Workbook
Create a Macro to recalculate the worksheet
►Go to→Developer→Visual basic→Right click sheet→Module→Paste below code
Finally Insert a shape and assign it the Macro
►Go to→Insert→Shapes→Right-click Shape→Assign Macro
That’s All!
Download Template RANDOM DATES AND TIME
Recent Comments