There are times when generating a list of dates comes in handy. Perhaps you need to log something every day for an entire year.
Or maybe you’re tracking stock prices and want to skip the weekend and only use weekday dates. How about a list of dates for each week of the year? Or just month ending days?
Dates for all of these examples can be created with Excel by filling a series.
Here is a bullet list of steps to follow.
- Enter a starting date into a cell
- Right click the cell’s fill handle, drag down and back up, and release the mouse handle
- Click Series from the pop-up menu
- This brings up the Series dialog box, or you can use Edit » Fill » Series… and skip these last two steps.
- Select Columns for Series in
- Make sure Date is selected for Type
- Make sure Day is selected for Date Unit
- Leave Step value as 1 for days
- Enter an ending date for Stop value
- Click OK
This will give you a series of days between the starting date you entered in the worksheet, and the Stop value date. Changing the Date unit to Weekday will exclude all the weekend dates.
If you change the Step value to 7 you will get weekly dates. If you want a Monday date each week, then pick a Monday date for your start date. If you want Friday dates, pick a Friday start date.
Leaving the Step value to 1 and changing the Date Unit to Month will generate, you guessed it, a series of monthly dates. Your starting date will determine which day is being replicated. Should your starting date contain the last day of the month, Excel is smart enough to generate a list for the last day of each month.
If the date series is not what was expected, it can be undone by using the keyboard shortcut Ctrl+z. I used this often as Rows is the default for Series in and I kept forgetting to change it to Columns about every fourth time.
Generating a list of dates in Excel works in versions 2000, 2002, 2003, 2007, 2010, and Excel for Mac 2008.