Welcome to Excel Jet Consult blog post. In this tutorial, we want to learn how to use one of the sophisticated functions in Excel named RANDBETWEEN
RANDBETWEEN returns a random number between numbers specified. The function is elegant to generate random date.
RANDBETWEEN Syntax =RANDBETWEEN(bottom, top)
We want to generate random contact number and dates in columns B and C respectively. The input values for random contact number are 08035630099 and 08037905787 in cells E16 and E17 respectively while the input values for random Dates are 01/01/2019 and 31/12/2019 in cells G16 and G17 respectively
To generate the random Contact Number:
In cell B2, execute the formula: =RANDBETWEEN($E$16,$E$17)
Click Enter and copy the formula down. Note, Absolute Cell Reference was applied to cells E16 and E17. This ensure that the cell reference in E16 and E17 remain fixed. We’ve been able to randomly generate contact number possible between the input values
To generate the random Dates:
- In cell C2, execute the formula: =RANDBETWEEN($G$16,$G$17)
- Click Enter and copy the formula down using fill handle. We have the output below
Next, we need to apply Date Number Formatting to display the actual date. To do that
- Select all the data using CTRL + SHIFT + Down Arrow and CTRL + Backspace to move us
- In the Number group of the Home tab, select Short Date from the drop-down. Task completed
We’ve learnt how to use the RANDBETWEEN function which is handy to quickly generate values between numbers and dates