In this article, we will learn to generate random date in Excel. You can use the mentioned formula as a date randomizer too.
Scenario:
Let's say we need random dates between two given dates for any given reason. So how do we do that?
How to solve the problem.
The dates are basically serial numbers in Excel. So we just need a formula where we can input dates as numbers and get the return result in date format. Here the solution to the problem depends on the condition. Possible conditions can be as follows.
So we will get to all the above problems but for that first we need to understand a little about the Excel RANDBETWEEN function.
The first problem is where the two dates are given in as direct argument to the function.
Use the formula:
date1 : the bigger date as top
date2 : the smaller date as bottom
Example: Create a List of Random Dates Between Two Defined Dates.
Here we have given two different dates in cell D3 & D4.
Now we need a bunch of random dates between the two given dates.
Now we will be using the formula to generate the date values.
Use the formula:
Explanation:
As you can see the formula returns the date value between the two given dates.
Now the second formula for another problem is getting random date within an year. So assume we need date values from the year 2019. We will use the DATE function with the RANDBETWEEN function.
Use the formula:
Explanation:
After changing the cell to Short date format.
Now drag down and right to generate as many number of date value as you want.
As you can see the formula works fine and returns the random date values within year 2019.
Now we switch to a third kind of formula which is generating random date values form the given list of date values. For this we can use either of the two formulas. First is using it when the given list of date value is small.
Use the formula:
Explanation:
Now drag down or right to get as many number of date values. But first freeze the cell reference.
As you can see the formula returns all the date values from the given list of date values.
Now for the fourth kind which is long list of date values
For this, we will be needing the use the following functions:
Now we will make a formula out of these functions. Here we will give the range to the INDEX function from where it can generate different values from the list.
Use the formula:
Explanation:
Now drag down or right to get as many number of date values. For this you don't need to freeze any reference as the named range is already a fixed reference.
As you can see the formula returns all the date values from the given list of date values.
As you can see in the above snapshot the outcomes of the formula.
Notes :
Hope this article about how to generate random date values in Excel is explanatory. Find more articles on TEXT functions here. Please share your query below in the comment box. We will assist you.
Related Articles
Get Random number From Fixed Options
Get Random numbers between two numbers
Excel Random Selection: How to Get Random Sample From a Dataset
How to use the CHOOSE Function in Excel
How to use the RANDBETWEEN Function in Excel
Popular Articles
50 Excel Shortcut to Increase Your Productivity
If with conditional formatting
Convert Inches To Feet and Inches in Excel 2016
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.