Calculating with month

Using the DATE function

By Neale Blackwood

Do you need to increment dates by whole months? For example do you need to add 1 month to 15/5/2009 to arrive at 15/6/2009? You could use the DATE function, but there is an easier function, EDATE, that is designed for this task.

In Excel 2007 this is a standard function. In Excel 2003 and earlier versions it is part of the Analysis ToolPak Add-In (see note below on installing the add-in). If there is a date in A1 then to add one month to that date you could use =EDATE(A1,1).

The corresponding DATE function would be more complex =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))

Warning: As with any date related formula there may be issues around February or between months with varying numbers of days. For example the two formulae will give different results if the date in A1 is 31/1/09. EDATE will return 28/2/09 (last day in February) and the DATE function will return 3/3/09 – missing February altogether. You may need to use an IF function to ensure the final result you require.

Note: Analysis ToolPak installation. Click the Tools menu, then click Add-ins, then check Analysis ToolPak (which should be at the top of the list) then click OK. You may need the Office installation CD.