Without EDATE
The other method you could use will sometimes yield a result that's different from the result EDATE provides. Again, ensure you have the date 31-Aug-2007 in cell A1. Click in B2 and enter in the following formula:
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
Using this formula returns the result of 1-Oct-2007, even though A1 houses the date 31-Aug-2007, because of the syntax of the DATE function:
DATEDIF(Year,Month,Day)
This function contains the following variables:
Year
Microsoft Excel interprets the year argument according to the date system you are using. By default, Excel for Windows uses the 1900 date system; Excel for the Macintosh uses the 1904 date system. Our formula has returned 2007.
Month
A positive or negative integer representing the month of the year from 1 to 12 (January to December). Our formula has returned September, which is the month from cell A1, plus 1.
Day
A positive or negative integer representing the day of the month from 1 to 31. If Day is greater than the number of days in the month specified, that number of days is added to the first day in the month. Our formula has returned 31.
Because no date of 31 September 2007 exists, Excel has returned 1-Oct-2007.
Remember, EDATE gave the result as 30-Sep-2007.
As you can see, there are differences between the two methods you can use when adding or subtracting months from a date. Choose which one suits your needs best but be aware both methods will not always yield the same result. It depends on how you determine a "month."
