Adding/Subtracting n Month(s) To/From a Date
Excel: Adding/Taking n Month(s) to a Date. Add Months to an Excel Date
Got any Excel/VBA Questions? Free Excel Help .
Excel: Returning Week Number
Excel is often used to work with Dates and it's well suited to do so. 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!
To take 1 month from the same date we would use;
=EDATE(A1,-1)
This yields the result 31-Jul-2005 which may, or may not be what you expect.
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!
Got any Excel Questions? Free Excel Help.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets.
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
