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.


Random dates image

To generate random Weekdays only use a combination of NETWORKDAYS and WORKDAY to filter out weekends as suggested by Richard Young on Linkedin;


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;


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))



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

Cells recalculating image

Create a Macro to recalculate the worksheet

►Go to→Developer→Visual basic→Right click sheet→Module→Paste below code

create Macro

Finally Insert a shape and assign it the Macro

►Go to→Insert→Shapes→Right-click Shape→Assign Macro

Assign Macro

assign macro2


That’s All!


Related Articles

Generating Lottery Numbers in Excel

Generating Strong Password or Encryption Keys