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)

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;

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

RANDOM TIME

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

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!

Download Template RANDOM DATES AND TIME

Related Articles

Generating Lottery Numbers in Excel

Generating Strong Password or Encryption Keys

Pin It on Pinterest

Share This

Spread the Good News!

Do me favour, please? Share this with your friends!

Thanks For Commenting!

Awesome for you to comment! Please share the post with your friends.