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.

7 comments
jshewsbury
jshewsbury

Hey, this is a very helpful trick. Thank you very much, hope you can share more in the future. All the best!!!

dmolter
dmolter

Helps if you read ALL the instructions -ha

n2add
n2add

Make sure you have installed the Analysis Pack as instructed in the article if the EDATE function is not available. I installed it in Office XP and it was there and worked.

dmolter
dmolter

=DATE(YEAR($A2),MONTH($A2)+B$1,DAY($A2)) Works if you don't have the EDATE function avail.

dmolter
dmolter

Doesn't appear to be a function in MS Office 2003

jody.burton
jody.burton

Using a leap day (i.e. 2/29/2012) as the anchor date and going forward or backward 12 months returns the final day in February, not the first day in March. If you go forward 48 months, it will return the 29th. On the other hand, using February 28th of a non-leap year as the anchor date and going forward or backward to a leap year will return the 28th, not the 29th.

spiras
spiras

...i.e. =EDATE($A2,B$1) is equivalent to =DATE(YEAR($A2),MONTH($A2)+B$1,DAY($A2)) The longer version allows playing around with the year and the day as well.

Editor's Picks