Creating a series of random dates in Excel allows you to test your spreadsheet's functions on a variety of dates or look at projections for a range of future dates. Excel has a built in random number tool that, when combined with the DATE function, lets you specify a range of dates to pick random days from. After creating the random dates, you will also need to convert the cells to display the dates in the correct format and lock the selected dates as static values.
Click the first open cell you want to fill with a random date. Type or paste the following function into the cell: =RANDBETWEEN(DATE(),DATE())
Place the cursor inside the parentheses after the first instance of "DATE," and type the starting date in the following format: year, month, day. For example, "2014, 1, 1" will start on January 1, 2014.
Move the cursor to the remaining set of empty parentheses, and enter the ending date in the same format. You will now have a formula that looks like the following example, which would create dates between January 1, 2014 and January 1, 2015: =RANDBETWEEN(DATE(2014, 1, 1),DATE(2015, 1, 1))
Press "Enter." Excel will display a five-digit number in the cell. Excel uses these numbers to identify dates internally.
Click the cell again. Drag the box in the lower-right corner of the cell in any direction to populate adjacent cells with the same formula. You can also copy and paste the cell to any location.
Select the row, column or set of individual cells containing the random date numbers. Right-click anywhere inside the selected area and press "Format Cells."
Pick "Date" from the Category menu and press "OK." Excel will convert the five-digit numbers into readable dates.
Press "Control-C," with all the date cells still selected, to copy them. Press "Control-V" without changing your selection to paste over the existing data. Press "Control" to open the paste options box and click "Values" (the "123" icon). This step permanently converts the formulas into dates. Without doing so, the dates would change every time you modified your spreadsheet.
- If your spreadsheet has existing formatting in the date cells, select "Values & Number Formatting" or "Values and Source Formatting" from the Paste Options box instead of "Values" to preserve it.
- Information in this article applies to Microsoft Excel 2013, 2010 and 2007. It may vary slightly or significantly in other versions.
- Stockbyte/Stockbyte/Getty Images