 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!