What does it do?

Generates random dates, in between a start date and an end date

Formula breakdown:

=RANDBETWEEN(bottom, top)

What it means:

=RANDBETWEEN(starting date for random date generation, end date for random date generation)


If you need to create a quick sample data set of Dates, the RANDBETWEEN function in Excel is very handy to use.

All you need is to provide a start Date and an end Date. Then the function will immediately generate a random Date following that Date range you have provided.

This is how it is done below (go to the bottom of the page if you want to see the animated gif tutorial):



STEP 1: We need to enter the Randbetween function



STEP 2: The Randbetween arguments: Enter the Start Date (bottom) and the End Date (top).

We need to use the DATE function to create the Date inputs for the bottom and top Dates.

In our example, we need to generate a random date from January 1, 2016 (bottom) to December 31, 2016 (top).

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


STEP 3: However notice that our random dates are in the numerical format:

Randbetween Date 01


Highlight the dates and change it into the Short Date Format:

Randbetween Date 02


Now we have our Random Dates:
Randbetween Date 03

NB: Each time you make a change in a workbook that contains the RANDBETWEEN function, the formulas will get updated and new values will appear!  This will also happen each time you press the F9 key..try it!

To lock in the formulas, you will need to hard code the formulas by selecting the data, CTRL+C, Right Click and Paste As Values.

Randbetween Dates


How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel

If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest


Share on Facebook


Tweet about this on Twitter


Share on LinkedIn


Share on Google+


Related Posts

Extracting Data with Excel’s LEFT Formula What does it do? It returns the first character or characters in a text string, based on the number of characters you specify. Formula breakdown: =LEFT(text, ) What it means: =LEFT(look in this cell, extract X characters) There are times when you will need t...
VLOOKUP with Multiple Criteria in Excel ‘VLOOKUP’ is now so common that everyone who has ever been working with Excel has heard of it. Not necessarily because they know how to use it, but because some savvy Excel-user always talks about it at the office. Advanced users have seen the use of adding more than one...
Change Phone Area Codes with Excel’s REPLACE Formu... What does it do? Replaces part of a text string, based on the number of characters you specify, with a different text string Formula breakdown: =REPLACE(old_text, start_num, num_chars, new_text) What it means: =REPLACE(this cell, starting from this number, all the ...
Count Formula in Excel What does it do? Counts the number of cells that contain numbers Formula breakdown: =COUNT(value1, ...) What it means: =COUNT(range of cells to check, ...) Ever had a column of data and wanted to check if all of the values contain valid numbers? It would ...