One common request is to use Excel to add any desired number of months to a date. For example, if A1 houses the date 31-Aug-2005 you may wish to add (or take) 1 month to that date. There is always going to be controversy as to what number of days should be added/taken to represent a month. This is because not all months have the same number of days.

EDATE

The EDATE function/formula is part of the Excel Analysis Toolpak which must be installed for its use. That is, Tools>Add-ins and check Analysis Toolpak. Then you you can use the EDATE function as shown below to add (or take) 1 month to the date in A1 (31-Aug-2005).

=EDATE(A1,1)

This formula yields a result of 30-Sep-2005. Not the 31-Sep-2005 as there is no such date!

The EDATE function gives priority to the notion that you are adding 1 month, not the number of days related to a month. In this case 31st of September does not exist, but as you are adding 1 month to an August date you get the August+1 equivalent, it returns the last available date in September:

To take 1 month from the same date we would use;

=EDATE(A1,-1)

Without EDATE

The other way that we use will sometimes yield a different result than EDATE. For example, if we do not use EDATE, but the formula below, we get a result of 1-Oct-2005 even though A1 houses the date 31-Aug-2005. Remember, EDATE gave the result as 30-Sep-2005

=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))

As you can see, there are differences with each of the 2 methods. Choose which ones suits your needs best but be aware both methods will not always yield the same result!