 |
|
|
|
|
|
Got any Excel Questions? Free Excel Help . See Also: Day of Week || Week number || Calculate a Persons Age || Excel Formulas Tips Tricks etc || Excel Calendar for Valid Dates
Excel: Returning Week Number
Excel is often used to work with Dates and it's well suited to do so. See how Excel stores dates & times . 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 it's 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: Day of Week || Week number || Calculate a Persons Age || Excel Formulas Tips Tricks etc || Excel Calendar for Valid Dates
Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money
Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
GIVE YOURSELF OR YOUR COMPANY 24/7 MICROSOFT EXCEL SUPPORT & QUESTIONS