Create A Random Date Generator In Excel

In this post I am going to show you how to create a random date generator in Excel. I have seen this requested a number of times on blogs and forums recently so I thought it would be good to share how I would do it.

First I’ll explain how dates are stored in Excel because this will help our understanding as to how we can create random dates. If you want to skip to the random date generator building, then use the contents section below to skip to each section instead.

1. How Are Dates Stored In Excel?
2. Completely Random Date Generator
3. Random Date Between 2 Dates
4. Random Date Within 6 Months of Today
5. Random Date Generator Using VBA

How Are Dates Stored In Excel?

Quite simply, a date in Excel is just a number – that’s it! Microsoft refer to these numbers as serial numbers and they are used by Excel for date and time calculations.

When a cell containing a number is formatted using a proper ‘Date’ format it will present on-screen as a recognizable date. Indeed, you can create your own custom date formats in Excel so they present exactly the way you’d like them to.

As far as Excel is concerned, the beginning of time started on 01/01/1900 and this date is represented as the serial number 1.

You can see below, column A contains a series of numbers and column B shows you the date that they represent. Formatting a cell as a ‘Date’ tells Excel to present the number as a date.

Each consecutive date from the seed date (sometimes referred to as the ‘epoch’) is incremental from the number 1. Therefore, the 2nd January 1900 is represented as the number 2 in Excel, and so on until the present day and beyond.

What is today’s ‘number’?

At the time of writing, 01/16/2021, the date number that represents today is 44212. Check it out for yourself. Type 44212 into any cell then in the Format section, change the cell format to ‘Short Date’ and you’ll see it represented as 01/16/2021.

So 01/16/2021 is 44211 days after 01/01/1900.

Completely Random Date Generator

If all you’re interested is generating a random date, then we just need a random number between 1 and, let’s say 100,000. You could probably work out for yourself that 100,000 translates to 10/14/2173 so that should give us a wide enough range of dates to play with.

To generate a random date, we’ll use the RANDBETWEEN function. This, not surprisingly, returns a random number between a lower and upper limit.

1. In cell A1, enter ‘=RANDBETWEEN(1, 100000)’
2. Hit enter

This will generate a random number between 1 and 100,000. Then we simply format the cell as a date by going to Home –> Number Section and selecting ‘Short Date’ from the drop down list.

Now we have a simple random date generator. To refresh the date generator, you have lots of options:

1. Double-click into the cell, then hit Enter on the keyboard
2. With the cell selected, click into the formula bar with the mouse, then hit Enter on the keyboard
3. With the cell selected, hit F2 key, then hit Enter on the keyboard
4. Press the F9 key

The F9 key refreshes calculations on the active sheet. It’s a useful keyboard shortcut to remember. For more useful shortcuts, check out this handy guide from ExcelJet – 222 Excel shortcuts for Windows and Mac

Random Date Between 2 Dates

It’s more likely you’ll want to generate a random date that falls within a particular time frame. We can modify our previous work to do this.

First we need to know the numbers that represent the first and last date of our date range. The easiest way to work this out is to insert the two dates into 2 separate cells, then format both cells as a number.

I’m going to use 01/01/2021 and 06/01/2021 (US format) for my example.

1. Enter the dates into cell A1 and A2 respectively
2. Select both cells and click the Home tab on the ribbon, then in the Number section, select ‘Number’ from the drop down list
3. The dates will now be formatted as numbers (44197 and 44348)
4. In another cell, use the RANDBETWEEN function and pass in these numbers as the lower and upper limit
5. Format this cell as a short date by clicking on the Home tab, and selecting ‘Short Date’ from the drop down in the Number section

This creates a random date generator between these 2 dates. You can refresh the date generator by hitting F9 on the keyboard.

Random Date Within 6 Months of Today

This technique introduces another built in Excel function called TODAY. The today function returns the serial number of the current date.

We’ll also use EDATE to calculate the date serial number 6 months from today. These 2 functions combined with the RANDBETWEEN function will allow us to create a random date generator that returns a date within the next 6 months of today.

The formula looks like this:

=RANDBETWEEN(TODAY(),EDATE(TODAY(),6))

To get this random date generator to work, we pass in today’s date using the TODAY function as the lower limit, and the EDATE function, using TODAY and 6 and the parameters, to calculate 6 months from today for the upper limit.

This function is dynamic and will work tomorrow, and the next day, and the next, and so on as TODAY will always return the current date.

Random Date Generator Using VBA

Using VBA to create a random date generator in Excel only requires a few lines of code. We’ll create a function that you can then access on your spreadsheet like you would any other function in Excel.

In a new VBA module, copy and paste the code snippet below.

Once you’ve pasted this code into a new module, head back to Excel and in any cell, you can use the new function on your spreadsheet:

=GenerateRandomDate(startDate, endDate)

This function will return a random date between user supplied start and end dates. Remember to add double quotes around your dates!

Summary

I have shown you a few different ways to create a random date generator in Excel. We simply need to manipulate the underlying serial number that Excel uses to perform calculations on dates and times.

Hopefully you’ll find at least one of these techniques useful and will solve a problem you may be having.