Microsoft

Use EDATE() and relative addressing to create a quick date matrix in Excel

With a little planning, you can create an easy-to-implement date matrix in Microsoft Excel.

Excel's EDATE() function adds a specified number of months to a date. The result is a serial number, which you can quickly format to display a meaningful date. Combined with a little relative addressing, you can use this function to create a quick past- or future-date matrix.

Now, let's suppose you want to return future dates based on a date of birth, date of order, and so on. After entering the anchor dates, a matrix of future dates is just a few clicks away, but setting up the matrix headings is the key. You need to know the approximate number of months you want to track. Below, we have a simple matrix for evaluating dates 6, 12, 18, and 24 months into the future. Notice that I've entered just the month as a value and not included any kind of text description, such as months.

To create the matrix, enter the following formula into B2:

=EDATE($A2,B$1)

Where $A2 identifies the anchor date and B$1 identifies the number of months to add to the anchor date. By using relative addresses for both arguments, you can quickly copy this formula to create the matrix. First, copy the formula in B2 to C2:E2. Then, double-click E2's fill handle to copy row 2's formulas to the remaining cells in the matrix (B3:E6). Be sure to apply a date format to the formula cells.

The relative addresses make quick work of this matrix! You can select any formula in the matrix to examine how it works. For example, the formula in D5 refers to the anchor date in A5 and the monthly interval of 18 in D1. As a result, this formula adds 18 months to May 1, 2013, and returns 41944, or November 1, 2014.

To return past dates, enter the second argument as a negative value. But you don't have to adjust all those formulas - only the headings!

If this function returns the #NAME? error in Excel 2003, install the Analysis ToolPak add-in as follows:

  1. Choose Add-ins from the Tools menu.
  2. Select Analysis ToolPak and click OK.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks