EXCEL VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS

Ozgrid, Experts in Microsoft Excel Spreadsheets

Adding/Subtracting n Month(s) To/From a Date

| | Information Helpful? Why Not Donate.

 

Excel: Adding/Taking n Month(s) to a Date. Add Months to an Excel Date

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 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: 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 VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS

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

Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates