Generate a List of Dates in Excel

by Gregory on June 28, 2010

Post image for Generate a List of Dates in Excel

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.

Related Posts Plugin for WordPress, Blogger...
Moatasem June 28, 2010 at 10:21 pm

fill series is a great tool in ms excel, because it save our time

» What is the usage of Trend Check box in Series dialogue box?

Gregory June 29, 2010 at 1:04 am

Good question. Apparently it generates or projects values that fit a straight or linear trend line. It’s used if here is more than one starting value in a series and you want Excel to generate the trend. However it’s not applicable for generating dates.

Rishi Paamar January 13, 2013 at 7:23 am

very nice full notes of excel 2003

Comments on this entry are closed.

{ 1 trackback }

Previous post:

Next post: