MrExcel Publishing
Your One Stop for Excel Tips & Solutions

EOMONTH function


Posted by Tim on October 19, 2001 12:43 PM

I often use the EOMONTH function which is a part of the analysis toolpack. However, I'm working on a spreadsheet that will be sent out to numerous people, many of whom will not have the Analysis ToolPack installed. Is there any way to build the EOMONTH function into the spreadsheet or a similiar function I can use?

Thanks!


Posted by IML on October 19, 2001 12:50 PM

Give
=DATE(YEAR(A1),MONTH(A1)+A2+1,1-1)
a try where A1 holds your date and a2 is number of months.
Ie =EOMONTH(A1,A2)

Good luck.

Posted by IML on October 19, 2001 12:54 PM

If my math is right, you could change 1-1 to zero, too.

Posted by Mark W. on October 19, 2001 12:55 PM

This does the same thing...

=DATE(YEAR('Start Date'),MONTH(A2)+'Months'+1,1)-1

Posted by Barrie Davidson on October 19, 2001 1:11 PM

IML.....

You'll get a kick out of this (after your "doh" response earlier today). I'm banging away to come up with a solution and didn't even realize you could use zero as a day. So.....here's my solution

=DATE(YEAR(A1),MONTH(A1)+1,IF(OR(MONTH(A1)+1={1,3,5,7,8,10,12,13}),31,IF(OR(MONTH(A1)+1={4,6,9,11}),30,IF(MONTH(A1)+1=MONTH(DATE(YEAR(A1),MONTH(A1)+1,29)),29,28))))

Similar disaster to yours, but I'd venture to say that mine is a bigger disaster than yours!!

Cheers
Barrie :)

Posted by IML on October 19, 2001 1:20 PM

Re: IML.....

I didn't either. I really put 1-1 in there and then realized after the fact "maybe I could put in a zero". Looks like you can put in negatives in either the month or day field. Crazy. Its alway good to know your not the only one going nuts on occasion!