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.
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).
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;
This yields the result 31-Jul-2005 which may, or may not be what you expect.
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
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.
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft