Software optimize

Generate random dates within a specific date range

Excel's RAND() functions generate random values between 0 and 1. When you need a little more flexibility try RANDBETWEEN().

Most users are familiar with RAND(). This function doesn't take an argument; it just returns a random value between 0 and 1. To generate a random number between 0 and 10, you'd multiply the results of RAND() by 10; to generate a random number between 0 and 100, you'd multiply by 100, and so on. If you want random integers, you'd wrap the RAND() function in an INT() function.

As just shown, generating random numbers between 0 and another number is relatively easy. Generating other types of random values, such as dates, isn't. It can be done, but it requires a convoluted formula that's hard to remember. Fortunately, that's unnecessary, thanks to RANDBETWEEN().

Excel's RANDBETWEEN() function returns a random integer number between two numbers, the function's arguments, in the form

RANDBETWEEN(bottom,top)

where bottom is the lower value and top is the upper value. For instance, the function RANDBETWEEN(27,100) would return random values between 27 and 100. If you transpose the two arguments, the function will return an error.

In Excel 2003, you must install the Analysis ToolPak add-in to access this function, as follows:

  1. Choose Add-Ins from the Tools menu.
  2. In the resulting dialog box, click the Analysis ToolPak option.
  3. Click OK.

With just a little more work, you can use RANDBETWEEN() to generate random dates for a specific time period. You'll include the DATE() function to specify the bottom and top values as dates, using the form

RANDBETWEEN(DATE(bottomdate),DATE(topdate)

For example, to return random dates between January 1, 2012 and January 30, 2012, you'd use the following function:

=RANDBETWEEN(DATE(2012,1,1),DATE(2012,1,30))

Being random, resulting dates might repeat, but they'll always fall within the specified dates.

When using RAND() or RANDBETWEEN() you'll probably want to turn off Excel's automatic recalculation or use Paste Special's Values option to replace the function with its evaluated result. That's because these functions recalculate every time you change the sheet.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

1 comments
dqkennard
dqkennard

I had a situation where I had to "sanitize" existing data for use in training. The data included birth dates. I didn't want to leave them as-is because that could aid in identifying the underlying real person, but I also didn't want to use a completely random date, or even the sort of random date with a range as described here. What I did was to start with the existing date and fuzz it up a little as follows: =birthdate+randbetween(-30,30) (format as date, of course,) So, the date was still "reasonable" but was off from the real date by 0-30 days in either direction.