<$BlogRSDURL$>

June 12, 2004

The DATE function, Part 2

It's a funny quirk of business. "Payment at 30 days" doesn't necessarily mean payment at 30 days, it really means exactly one month after. For example, if I add 30 days to today's date (06/12/04), the answer is 07/12/04 only because June has 30 days. Try adding 60 days and the result will be 08/11/04. What should really be calculated is today plus 2 months which is 08/12/04.

With this in mind, there is a very useful function called the EDATE function. It works like this, =EDATE(Date,Months) so if I want to add 2 months to today I can enter =EDATE(TODAY(),2). (Don't forget that =TODAY() gives today's date)

The problem is that the Analysis Toolpak must be installed for EDATE function to work. To check whether it is installed, select Tools, Add-Ins and the dialog box will appear like this.



And if you don't have the Analysis Toolpak? Here is a workaround that I call EDATEPLUS. It uses the DATE function to add or subtract the number of months you want, and if the days of the calculated date exceed the number of days that actually exist in that month, it will calculate to the end of the month instead, this being selected by the IF function. (For example, if today was 01/31/04, then one month later will be 02/29/04, not 03/01/04).

Hold on to your seats...

=IF(NOT(DAY(A1)=DAY(DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1)))),DATE(YEAR(A1),MONTH(A1)+B1+1,0),DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1)))

A1 is the original date and B1 is the number of months to add or subtract. Place the formula in any cell other than A1 or B1.

Basically it works like this. If the day of the calculated date (from the original date plus or minus the required months) does not equal the day of the original date, calculate to the end of the month instead. (Actually the end of month can be calculated by using the EOMONTH fuction also available with the Analysis Toolpak, or as =DATE(YEAR(A1),MONTH(A1)+B1+1,0) without the Analysis Toolpak)

Here's a Workbook example to have a look at. If you don't have the Analysis Toolpak installed, the EDATE and EOMONTH formulas will not work and you will get a #NAME? error.

There is also an EDATEPLUS Addin with installation instructions included on the right sidebar. Beware that it will only work on the computer(s) it is installed in. The workarounds above and in the Workbook example should work in any computer 100% of the time.

Comments:
Thanks, this was exactly what I was looking for!!
 
Thank you!!!!
 
Post a Comment

This page is powered by Blogger. Isn't yours?