# Excel Formulas for Random Numbers and Dates

Using Excel's RAND and RANDBETWEEN functions to create random numbers and dates and sets of random numbers and dates.

Last updated on 2019-01-28 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 — approximately — 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 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:

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:

## 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:

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

## Give DMW’s Tips Your Support

ContributionIf you intend making use of Tips in any of your professional or commercial work and would like to make a donation in recognition of the time I spend publishing them, then please click Contribution and tell me how much you wish to donate. In response I’ll email you an invoice for payment by PayPal.

Thanks, in anticipation.