How to Generate Random Dates in Excel

Being able to generate random dates in Excel is a valuable skill to have. For example, you may need to create some mock data. However, it can be tedious to think of random dates manually. Because of this, in this tutorial, you’ll learn how to generate random dates in Excel. In order to do this, you’ll learn how to use the Excel RANDBETWEEN() function which lets you generate numbers between two numbers.

By the end of this tutorial, you’ll have learned the following:

• How Excel stores dates and why this is important to know when generating random dates
• How to use the RANDBETWEEN() and DATE() function to generate random dates between two dates in Excel

How Excel Stores Dates

Excel has a clever way of storing date and time values. The date portion is stored as an integer value, with January 1, 1900, as the first date. Any date beyond that is incremented by 1. For example:

• January 3, 1900, would be equal to 3
• November 23, 2023, would be 45253

This is important because it allows you to understand how generating random dates works in Excel.

Let’s take a look at how the RANDBETWEEN() function works in Excel:

RANDBETWEEN(bottom, top)

You can see in the code block above that the function accepts two arguments:

1. bottom= is the lowest integer that the function should return
2. top= is the largest integer that the function should return

The function then returns a random number between the two numbers passed into the function. Because Excel stores dates as numbers, we can pass dates into the function to generate a random date.

Generate a Random Date Between Two Dates in Excel

Generating a random date between two dates in Excel is a very simple task, as shown above. Let’s walk through a step-by-step example of how this works to give a complete overview.

Step 1: Use the RANDBETWEEN() Function to Generate a Random Date

In the example above, we used the Excel RANDBETWEEN() function to generate a random date between two dates. Rather than passing in numbers, we passed in values using the DATE() function that represent January 1, 2023 and December 31, 2023. Since Excel represents dates as numbers, this works quite well. However, as you can see above the function returned a number.

Step 2: Format the Number as a Date

Change the format of the number to a date using the formatting dropdown. Since the formatting just modifies the display of the value, changing it to either a short date, long date, or custom date doesn’t matter.

Step 3: Verify the date formatting

When you select a formatting style, Excel will change the value to display a date rather than an integer.

Step 4: Use the Excel Fill Handle to Generate Multiple Random Dates

By clicking and dragging the fill handle down, you can generate multiple random dates in Excel. In the example above, we dragged it down to row 8 to generate seven random dates. Because we hardcoded the values into the function, they don’t get modified.

Conclusion

In this tutorial, you learned how to use Excel to generate random dates between two dates. Because Excel stores dates as numbers, you can use numerical functions such as RANDBETWEEN() with dates. That’s exactly what you learned how to do in this tutorial. You learned how to pass two dates into the RANDBETWEEN() function to generate dates between the two dates. This has many different applications, including generating mock data. This is a much better approach than trying to generate it randomly, since the data will be truly random.

Additional Resources

To learn more about related topics, check out the tutorials below:

• How to Combine Date and Time in Excel
• How to Separate Date and Time in Excel
• Excel RANDBETWEEN() Function: Official Documentation