How To:
Create Dates for Quarterly Time Series in Excel
Microsoft Excel has limited capabilities of dealing with dates. In particular it is not designed to handle macroeconomic time series data that are observed at quarterly intervals. To generate a series of dates for such quarterly data in Excel requires that a particular month of each quarter be designated as the indicator for that quarter.
Assume that we have a quarterly macroeconomic time series that begins in the first quarter of 1990 and continues though the last quarter of 1992. Let the middle month of each quarter is utilized as the date indicator for that quarter (there is nothing sacred about the middle month, the first or last month of the quarter could also be used).
To generate a date series for this varible in Excel, start by typing the month-year indicator for the first observation (either 2-90 or Feb-90 in this example) into a cell. Now select the cell immediately below the one containing the first date (same column, next row) and type the month-year indicator of the next observation (5-90 or May-90 in this example). For this example the date column would appear as indicated on the left.
Once the date series is started in this fashion, Excel will create the rest of the observations (for the third quarter of 1990 through the fourth quarter of 1992 in this example)
automatically. All that is required is to select both date cells and grab the fill handle and drag it down a sufficient number rows to accomodate the length of the data series. In this example 10 additional rows must be added.
The fill handle is grabbed by pointing the mouse at the lower right handcorner of the selected cells. You know you have grabbed the fill handle when the cursor indicator is replaced by a cross-hair. When you see the cross-hair, depress and hold the left mouse button and drag the cross-hair directly down the required number of rows. This will automatically fill in the column with dates incremented by three months (the difference between the dates in the two selected cells. Now there are valid and usable dates associated with your time series.