Date Table: Make a date with your data

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 2.1. Pull and drag down method

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 2.2. Create a series

This method requires you to find the series button.

Step 2.2a. 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 2.2b. If you want the dates to be reflected vertically, you need to make sure you select Columns under the Series in option.

Step 2.2c. You need to make sure that the Type option has been set to Date.

Step 2.2d. Ensure that the Day function has also been selected under the Date unit option.  

Step 2.2e. 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 2.2f. To define your date period, key in the last date you want under the Stop value option.  

excel table series.png

And with one final click on OK, tada! 

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