In cells H15 and H36 I have 10/31/09 and 1/31/10 respectively.
In J 15 and J36 I have the following formula:

=DATE(YEAR(H15),MONTH(H15-DAY(H15))+2,)
Cell J36 has With H36 instead of H15.
The intent is to identify the last date of the month identified in column H.

However, the results are:
H J
10/31/09 10/31/09 (This worked)
1/31/10 1/31/11 (This added a whole year)

The equation seems to work every where unless the date in column H is in
January.

Column L does something similar to calculate the end of the subsequent
month. It works in all cases. The formula I used for that is:
=DATE(YEAR(H36),MONTH(H36-DAY(H36)+2)+2,)

Why isn't the first formula working in every case?

TIA

Papa J

Hi,
If your data is exactly the same as posted your formula added a year because
in column H you have 1/31/10 and in column J 1/31/11 just a year so formula
works

"Papa Jonah" wrote:

> In cells H15 and H36 I have 10/31/09 and 1/31/10 respectively.
> In J 15 and J36 I have the following formula:
>
> =DATE(YEAR(H15),MONTH(H15-DAY(H15))+2,)
> Cell J36 has With H36 instead of H15.
> The intent is to identify the last date of the month identified in column H.
>
> However, the results are:
> H J
> 10/31/09 10/31/09 (This worked)
> 1/31/10 1/31/11 (This added a whole year)
>
> The equation seems to work every where unless the date in column H is in
> January.
>
> Column L does something similar to calculate the end of the subsequent
> month. It works in all cases. The formula I used for that is:
> =DATE(YEAR(H36),MONTH(H36-DAY(H36)+2)+2,)
>
> Why isn't the first formula working in every case?
>
> TIA
>
> Papa J

But as I indicated, the intent is to identify the last day of the month - the
intent is not to add a year. The rest of the cells did not add a year making
all the other cells with the desired results as opposed to this one example
from January.

"Eduardo" wrote:

> Hi,
> If your data is exactly the same as posted your formula added a year because
> in column H you have 1/31/10 and in column J 1/31/11 just a year so formula
> works
>
> "Papa Jonah" wrote:
>
> > In cells H15 and H36 I have 10/31/09 and 1/31/10 respectively.
> > In J 15 and J36 I have the following formula:
> >
> > =DATE(YEAR(H15),MONTH(H15-DAY(H15))+2,)
> > Cell J36 has With H36 instead of H15.
> > The intent is to identify the last date of the month identified in column H.
> >
> > However, the results are:
> > H J
> > 10/31/09 10/31/09 (This worked)
> > 1/31/10 1/31/11 (This added a whole year)
> >
> > The equation seems to work every where unless the date in column H is in
> > January.
> >
> > Column L does something similar to calculate the end of the subsequent
> > month. It works in all cases. The formula I used for that is:
> > =DATE(YEAR(H36),MONTH(H36-DAY(H36)+2)+2,)
> >
> > Why isn't the first formula working in every case?
> >
> > TIA
> >
> > Papa J

On Fri, 5 Mar 2010 10:34:01 -0800, Papa Jonah
<(E-Mail Removed)> wrote:

>In cells H15 and H36 I have 10/31/09 and 1/31/10 respectively.
>In J 15 and J36 I have the following formula:
>
>=DATE(YEAR(H15),MONTH(H15-DAY(H15))+2,)
> Cell J36 has With H36 instead of H15.
>The intent is to identify the last date of the month identified in column H.
>
>However, the results are:
> H J
>10/31/09 10/31/09 (This worked)
>1/31/10 1/31/11 (This added a whole year)
>
>The equation seems to work every where unless the date in column H is in
>January.
>
>Column L does something similar to calculate the end of the subsequent
>month. It works in all cases. The formula I used for that is:
>=DATE(YEAR(H36),MONTH(H36-DAY(H36)+2)+2,)
>
>Why isn't the first formula working in every case?
>
>TIA
>
>Papa J

To return the last day of the month, with a date in H15

=date(year(h15),month(h15)+1,0)

or, if you have Excel 2007+ or an earlier version with the Analysis Tool Pak
installed:

=eomonth(h15,0)


--ron

Thanks David. I don't understand your explanation why I should get what I
got - especially since the other cells did not add a year.
But your suggestion worked beautifully. The reason I didn't do that before
is I didn't figure it out!

Thanks

"David Biddulph" wrote:

> If H15 is 31/1/10, MONTH will return 12, you've then added 2 to make it 14,
> hence 31/1/11 sounds like the answer you would expect from that formula.
>
> I'm not sure why you are using =DATE(YEAR(H15),MONTH(H15-DAY(H15))+2,)
> Why not =DATE(YEAR(H15),MONTH(H15)+1,0) ?
> --
> David Biddulph
>
>
> "Papa Jonah" <(E-Mail Removed)> wrote in message
> news:3E34CEAB-EB47-4BD1-8291-(E-Mail Removed)...
> > In cells H15 and H36 I have 10/31/09 and 1/31/10 respectively.
> > In J 15 and J36 I have the following formula:
> >
> > =DATE(YEAR(H15),MONTH(H15-DAY(H15))+2,)
> > Cell J36 has With H36 instead of H15.
> > The intent is to identify the last date of the month identified in column
> > H.
> >
> > However, the results are:
> > H J
> > 10/31/09 10/31/09 (This worked)
> > 1/31/10 1/31/11 (This added a whole year)
> >
> > The equation seems to work every where unless the date in column H is in
> > January.
> >
> > Column L does something similar to calculate the end of the subsequent
> > month. It works in all cases. The formula I used for that is:
> > =DATE(YEAR(H36),MONTH(H36-DAY(H36)+2)+2,)
> >
> > Why isn't the first formula working in every case?
> >
> > TIA
> >
> > Papa J
>
>
> .
>