The straightforward way to add six months to a date in Excel is to use a formula of the form:

=DATE(YEAR(A29),MONTH(A2)+6,DAY(A2)) where your source date is in cell A2.

However, due to the varying length of months, if your source date happens to be, for example, 31/1/2011 and your formula adds 5 months, then the result of the calculation will be 1/7/2011. Perhaps not what you were expecting.

In order to get round this problem, Excel provides the EDATE function, which will correctly add months to a date and take into account varying month lengths. In our example, EDATE gives the correct result of 30/6/2011.

(If EDATE does not function in your version of Excel, check that you have the Analysis Toolpak installed).

Advertisements