future

Generating Random Future Dates,Time & Datetime in Excel

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

Leave a Comment:

Your email address will not be published. Required fields are marked *