<<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>>
Ozgrid, Experts in Microsoft Excel Spreadsheets

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

| | Information Helpful? Why Not Donate.

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL| More Free Downloads.. Best Value: Finance Templates Bundle

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 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 [email protected] 31 days after purchase date.


Instant Download and Money Back Guarantee on Most Software

Try out: Analyzer XL | Downloader XL | Smart VBA | Trader XL Pro (best value) | ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

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