Date Table: How to Make a Date with Your Data using Series

At some point in your career, you will most likely find yourself having to create a date table for whatever reason.

How should you go about doing this? Manually key in each date one by one?

Excel is way ahead of you, sista. There are two ways to go about this very, very easily and both methods require the same first step.


Step 1. Key in your start date in a cell.


Step A2. Pull and drag down (Method A)

Once you have your start date in place, you can simply adopt the pull and drag down method.

Click on the right bottom anchor of the cell, hold, pull the anchor down and release.

Easy enough, right? But what if you need to cover a five year period? Ten years? Your fingers might cramp from the intense holding and pulling down. There is a better way to go about it. 


Step B2. Create a series (Method B)

This method requires you to find the series button.

Step B2.1. Right-click on the bottom anchor of the cell with the first date. Hold the right-click while pulling your mouse down and up before releasing the right-click. A drop down list will appear with the Series option.  

Once the Series window has popped up:

Step B2.2. If you want the dates to be reflected vertically, you need to make sure you select Columns under the Series in option.

Step B2.3. You need to make sure that the Type option has been set to Date.

Step B2.4. Ensure that the Day function has also been selected under the Date unit option.  

Step B2.5. The Step value option sets the date intervals. For example, if you only require alternate dates, you should key in 2. That way, only subsequent dates 3-Jan-18, 5-Jan-2018 etc. will be reflected in the cells.  

Step B2.6. To define your date period, key in the last date you want under the Stop value option.  

Set how series of dates should fill in Excel Series

And with one final click on OK, tada! 

Admit it, you really want to open an excel sheet now to play with this, right?