DMW logo
tel 01732 833085
tonbridge · kent · UK

Excel Formulas for Random Numbers and Dates

Using Excel's RAND, RANDBETWEEN and DATE functions to create random numbers and dates, sets of random numbers and dates, and series of dates in date order yet separated by random numbers of days.

Last updated on 2019-03-01 by David Wallis.


Preface

For a new spreadsheet, numeric values, dates or both are I often need to test it. But it wasn’t until a client asked about how to populate a particular date set that I took to writing this piece. In their projects spreadsheet, the client required start and finish dates for each of their projects. They certainly did not want to input them manually.

In Fill Cells with Random Numbers I’ve presented a macro for filling cells with random numbers, but what my client required was an Excel formulae to do the job. I’ll build up to that particular formula by introducing Excel’s RAND, RANDBETWEEN and DATE functions.


Excel’s RAND Function

RAND has been with Excel since its year dot. Input the formula =RAND() in to a cell and Excel returns a positive decimal number greater than, or equal to, zero and less than one.

That number changes each time you do anything to the spreadsheet to cause it to recalculate.


Excel’s RANDBETWEEN Function

Unlike RAND, RANDBETWEEN allows you to specify the upper and lower limits of the range from which you want Excel to return a random number. The function has the two arguments:

RANDBETWEEN(lower_limit, upper_limit)

Input the formula =RANDBETWEEN(0, 100) into a cell and Excel returns a positive whole number greater than, or equal to, zero and less than, or equal to 100.

That number changes each time you do anything to the spreadsheet to cause it to recalculate.


Positive and Negative Randomness

Left to themselves, both RAND and RANDBETWEEN generate positive numbers. Putting a minus sign in front of RAND or RANDBETWEEN will cause Excel to return negative random numbers, and negative ones only:

=-RANDBETWEEN(0, 100) and =-RAND().

What’s needed to produce random numbers that are also randomly positive or negative numbers is a way to randomly change the sign after the = to either + or -. We’ll use RAND within an IF function to achieve this:

=IF(RAND()>0.5,1,-1) to product a -1 or +1, and then

=IF(RAND()>0.5,1,-1)*RANDBETWEEN(1000,2000) to produce a random number that is randomly either between -1000 and -2000 or +1000 and +2000.

To provide flexibility, this formula will provide a random number between -20 through zero to +100:

=IF(RAND()>0.5,RANDBETWEEN(0,100),-RANDBETWEEN(0,20))

If your concern is about skewing the randomness of your numbers, reflect on your composition of the RAND()>0.5 element and of your limits set for RANDBETWEEN.


Excel’s DATE Function

Excel works dates based on its built-in calendar which starts in the year 1900 and extends to 9999. If you want to do calculations on dates outside those years, Excel’s date functions won’t work.

If you input today’s date into a cell and then apply the General number format, you’ll get a number — round about — that’s what Excel calculates to be the number of days since the beginning of 1900.

To calculate the number of days between two dates, your formula takes the form =date2-date1. However, for obvious reasons, inputting the formula =2019-12-31-2019-01-01+1 does not have Excel calculating there are 365 days in 2019.

If you need to introduce a date directly into a formula, you engage the DATE function to cause Excel to refer to its internal calendar. DATE has this structure:

DATE(year, month, day)

It’s best to input a four-digit number for year to make certain Excel understands to which century you’re referring. month is a number from one to 12; day a number that is the day of your chosen month.

The order of the arguments is always the same, whatever the regional setting of your computer or the date format you’re using. Hence DATE is a useful reassurance that your dates will travel anywhere, uncorrupted.

So, a formula for the number of days in is this:

=DATE(,12,31)-DATE(,1,1)+1

To produce random dates, we’ll need to use Escel’s DATE functions.


A Random Date

Here’s a formula for creating a random number that represents a day in Excel’s built-in calendar for the year 2019:

=RANDBETWEEN(DATE(2019,1,1),DATE(2019,12,31))

You’ll need to ensure that you apply a date format to the cell containing the formula. That number, and therefore your date, will change each time you do anything to the spreadsheet to cause it to recalculate.


Dates for Projects

Now we’re set to create a set of dates as required by my client for their projects in 2019 as explained in the Preface:

Random project dates

All the dates are randomised. Be that as it may, the random Finish date must be later than the random Start date. Here’s my suggestion for the formulas to achieve this:

Random date range


Duration in Days

My client also needed the duration of each project. That’s calculated by subtracting the Start date from the Finish date. Or you might use this as an excuse to engage Excel’s DAYS function:

Excel's DAYS function

This is the function’s structure: DAYS(end_date, start_date).


Revised Project Dates

The Dates for Projects, above, proved satisfactory in all but a couple of respects. These were that each project had to start later than the start of the previous one and that all projects were to start within the current year — make that 2019 in this example.

Leaving the formulas in columns C and D as they were, I set about priming a January start for the first project, inputting the formula =RANDBETWEEN(DATE(2019,1,1), DATE(2019,1,31)) in cell B2.

Then I seeded the rest of the Start dates by copying down the formula =RANDBETWEEN(B2,DATE(2019,12,31)) from cell B3:

Cell B2

What a disappointment, to say the least! However many times you press key F9 to cause the formulas to recalculate, the dates and durations for those projects later in the list remained out of whack:

Cell B2 modifcation

Next to experiment with =B2+RANDBETWEEN(6,60), which is OK provided you're patient enough to keep pressing F9 to update the formula results to keep the Start dates within 2019:

Cell B2 revision

More work to be done.


Your Support for dmw TIPS

If you’re going to make use of Tips in any of your professional or commercial work, then please consider making a payment in recognition of the time spent publishing this website.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —

To say how much you want to pay and to receive
a tax invoice for a GBP (£ sterling) contribution —

Invoice

Thanks, in anticipation.

SSL Cerification

“I always wanted to be somebody, but now I realize I should have been more specific.”

Lily Tomlin