Generating random dates and/or timestamps in Excel is the same as generating any other random number. Keep in mind that no matter how dates and/or timestamps are formatted and possibly edited, the underlying values saved in cells are decimal numbers.
Number 1, formatted as a date, will represent January 1, 1900.
44.916 will represent December 21, 2022.
44.916,888111 will be representing December 21, 2022, at 9:18 PM.
Date and/or time stamps should be generated between two dates (within a defined range) so that nonsensical dates are not generated.
Functions that perform random number generation in Excel, the RANDBETWEEN function (can be used in order to generate random dates) and the RAND function (can be used in order to generate random dates and/or times), operate in just such a way.
The RANDBETWEEN function returns a random integer number between the numbers specified.
The RANDBETWEEN function syntax is as follows:
= RANDBETWEEN ( bottom; top )
A new random integer number is returned every time the worksheet is calculated.
In the following example, we will generate a random date between January 1, 2022, and December 21, 2022, using the RANDBETWEEN function:
The RAND function returns an evenly distributed random real number between 0 and 1.
The RAND function syntax is as follows:
= RAND ( )
A new random real number is returned every time the worksheet is calculated.
In the following example, we will generate a random time between January 1, 2022, and December 21, 2022, using the RAND function:
If we only want to generate dates using the RAND function, we can round our randomized result using the ROUND function:
Note:
These random values will recalculate every time your worksheet recalculates; convert them to values if needed.
You can use the WEEKDAY function nested inside of the IF function to check if your randomly generated date falls on the weekend and then add or subtract days from it.
Dig deeper: