Month end calculation

I am writing a formula to calculate the last and next month end e.g. if I
enter 28-02-06, the expected result will be liked 31-01-06 and 31-03-06.
28-02-06 will be stored in cell A1, and my expected result will be displayed
in A2 & A3. My formular is liked " =A1-31 , = A1+31. But because of "31"
has to change each month, therefore if doesn't work to my calcaulation.
Also, from the above example, the calculation for March is correct "31-03-06"
but the January is worng, it comes date on 28-01-06. But I need both result
at the end of the month. Can anyone help, thank you so much.

If you're always entering the last day of a month in A1 then for the last day of the previous month

=A1-DAY(A1)

and for the last day of the next month

=A1+32-DAY(A1+32)

alternatively yu could use the EOMONTH function from Analysis ToolPak and use

=EOMONTH(A1,-1)

and

=EOMONTH(A1,1)

=EOMONTH(A1,-1)
=EOMONTH(A1,1)


"Tony" wrote:

> I am writing a formula to calculate the last and next month end e.g. if I
> enter 28-02-06, the expected result will be liked 31-01-06 and 31-03-06.
> 28-02-06 will be stored in cell A1, and my expected result will be displayed
> in A2 & A3. My formular is liked " =A1-31 , = A1+31. But because of "31"
> has to change each month, therefore if doesn't work to my calcaulation.
> Also, from the above example, the calculation for March is correct "31-03-06"
> but the January is worng, it comes date on 28-01-06. But I need both result
> at the end of the month. Can anyone help, thank you so much.
>

the formula doesn't work, it came #NAME? as result. I don't know why.


Thanks
Tony


"JMB" wrote:

> =EOMONTH(A1,-1)
> =EOMONTH(A1,1)
>
>
> "Tony" wrote:
>
> > I am writing a formula to calculate the last and next month end e.g. if I
> > enter 28-02-06, the expected result will be liked 31-01-06 and 31-03-06.
> > 28-02-06 will be stored in cell A1, and my expected result will be displayed
> > in A2 & A3. My formular is liked " =A1-31 , = A1+31. But because of "31"
> > has to change each month, therefore if doesn't work to my calcaulation.
> > Also, from the above example, the calculation for March is correct "31-03-06"
> > but the January is worng, it comes date on 28-01-06. But I need both result
> > at the end of the month. Can anyone help, thank you so much.
> >

On Wed, 22 Mar 2006 16:33:30 -0800, Tony <[email protected]>
wrote:

>I am writing a formula to calculate the last and next month end e.g. if I
>enter 28-02-06, the expected result will be liked 31-01-06 and 31-03-06.
>28-02-06 will be stored in cell A1, and my expected result will be displayed
>in A2 & A3. My formular is liked " =A1-31 , = A1+31. But because of "31"
>has to change each month, therefore if doesn't work to my calcaulation.
>Also, from the above example, the calculation for March is correct "31-03-06"
>but the January is worng, it comes date on 28-01-06. But I need both result
>at the end of the month. Can anyone help, thank you so much.

A1: Some Date
A2: =A1-DAY(A1) (End of month prior to Some Date)
A3: =A2+63-DAY(A2+63) (End of month in month after Some Date)




--ron

the analysis toolpak has to be installed.

Tools/Add-Ins/Analysis Tookpak

"Tony" wrote:

> the formula doesn't work, it came #NAME? as result. I don't know why.
>
>
> Thanks
> Tony
>
>
> "JMB" wrote:
>
> > =EOMONTH(A1,-1)
> > =EOMONTH(A1,1)
> >
> >
> > "Tony" wrote:
> >
> > > I am writing a formula to calculate the last and next month end e.g. if I
> > > enter 28-02-06, the expected result will be liked 31-01-06 and 31-03-06.
> > > 28-02-06 will be stored in cell A1, and my expected result will be displayed
> > > in A2 & A3. My formular is liked " =A1-31 , = A1+31. But because of "31"
> > > has to change each month, therefore if doesn't work to my calcaulation.
> > > Also, from the above example, the calculation for March is correct "31-03-06"
> > > but the January is worng, it comes date on 28-01-06. But I need both result
> > > at the end of the month. Can anyone help, thank you so much.
> > >

Ron Rosenfeld wrote...
....
>A1: Some Date
>A2: =A1-DAY(A1) (End of month prior to Some Date)
>A3: =A2+63-DAY(A2+63) (End of month in month after Some Date)

February is the curse of all such simplifications. Put =DATE(2006,2,28)
in A2. A3 then returns 30-Apr-2006. Put =DATE(2006,9,29) and
=DATE(2006,8,30) in A2, and see that it's not just February.

The most reliable way to do this with built-in functions is

=DATE(YEAR(A2),MONTH(A2)+2,0)

and generally the equivalent for EOMONTH(x,n) is

=DATE(YEAR(x),MONTH(x)+1+n,0)

On 22 Mar 2006 22:32:55 -0800, "Harlan Grove" <[email protected]> wrote:

>Ron Rosenfeld wrote...
>...
>>A1: Some Date
>>A2: =A1-DAY(A1) (End of month prior to Some Date)
>>A3: =A2+63-DAY(A2+63) (End of month in month after Some Date)
>
>February is the curse of all such simplifications. Put =DATE(2006,2,28)
>in A2. A3 then returns 30-Apr-2006. Put =DATE(2006,9,29) and
>=DATE(2006,8,30) in A2, and see that it's not just February.
>
>The most reliable way to do this with built-in functions is
>
>=DATE(YEAR(A2),MONTH(A2)+2,0)
>
>and generally the equivalent for EOMONTH(x,n) is
>
>=DATE(YEAR(x),MONTH(x)+1+n,0)

Harlan,

Either you're sleepy or I'm missing the point you are trying to make.

Clearly if you change one of my formulas, you cannot expect the algorithm to
work!

However, with regard to your formulas, if you force A2 to be 28 Feb 2006, then,
as designed, A1 must have been a date in March, and A3 should, indeed be
30-Apr-2006 per the OP's request.

However, if you expect A3 to compute correctly when you are not forcing A2 to
be an end of month date, you are not understanding what I posted.


Entering your example dates in the DATA ENTRY CELL, A1, with the formulas I
posted being in A2 and A3, gives the following results:

A1: =DATE(2006,2,28)
A2: 31-Jan-2006
A3: 31-Mar-2006

And

A1: =DATE(2006,9,29)
A2: 31-Aug-2006
A3: 31-Oct-2006


--ron

Thanks JMB

I had installed the Tookpak and re-start my computer as well. I tried to
re-type my formular again and again. Also I did try to copy your formular
and paste, also doesn't work. I don't understand it.

Thank you for your help.
Tony


"JMB" wrote:

> the analysis toolpak has to be installed.
>
> Tools/Add-Ins/Analysis Tookpak
>
> "Tony" wrote:
>
> > the formula doesn't work, it came #NAME? as result. I don't know why.
> >
> >
> > Thanks
> > Tony
> >
> >
> > "JMB" wrote:
> >
> > > =EOMONTH(A1,-1)
> > > =EOMONTH(A1,1)
> > >
> > >
> > > "Tony" wrote:
> > >
> > > > I am writing a formula to calculate the last and next month end e.g. if I
> > > > enter 28-02-06, the expected result will be liked 31-01-06 and 31-03-06.
> > > > 28-02-06 will be stored in cell A1, and my expected result will be displayed
> > > > in A2 & A3. My formular is liked " =A1-31 , = A1+31. But because of "31"
> > > > has to change each month, therefore if doesn't work to my calcaulation.
> > > > Also, from the above example, the calculation for March is correct "31-03-06"
> > > > but the January is worng, it comes date on 28-01-06. But I need both result
> > > > at the end of the month. Can anyone help, thank you so much.
> > > >

Thank you so much Ron & Harlan

I got a little bit problem with this "=A2+63-DAY(A2+63) (End of month in
month after Some Date)", because I don't know how many month(s) before or
after I need to calcaulate until my boss will ask me. if if has me 3 months
after then I have to get the result at once, but from your formula I have to
change the no. of days to calculate. I will try to use the eomonth formula,
but meanwhile it doesn't work with my computer. I don't understand.

Thank you so much for great help.
Tony



"Ron Rosenfeld" wrote:

> On 22 Mar 2006 22:32:55 -0800, "Harlan Grove" <[email protected]> wrote:
>
> >Ron Rosenfeld wrote...
> >...
> >>A1: Some Date
> >>A2: =A1-DAY(A1) (End of month prior to Some Date)
> >>A3: =A2+63-DAY(A2+63) (End of month in month after Some Date)
> >
> >February is the curse of all such simplifications. Put =DATE(2006,2,28)
> >in A2. A3 then returns 30-Apr-2006. Put =DATE(2006,9,29) and
> >=DATE(2006,8,30) in A2, and see that it's not just February.
> >
> >The most reliable way to do this with built-in functions is
> >
> >=DATE(YEAR(A2),MONTH(A2)+2,0)
> >
> >and generally the equivalent for EOMONTH(x,n) is
> >
> >=DATE(YEAR(x),MONTH(x)+1+n,0)
>
> Harlan,
>
> Either you're sleepy or I'm missing the point you are trying to make.
>
> Clearly if you change one of my formulas, you cannot expect the algorithm to
> work!
>
> However, with regard to your formulas, if you force A2 to be 28 Feb 2006, then,
> as designed, A1 must have been a date in March, and A3 should, indeed be
> 30-Apr-2006 per the OP's request.
>
> However, if you expect A3 to compute correctly when you are not forcing A2 to
> be an end of month date, you are not understanding what I posted.
>
>
> Entering your example dates in the DATA ENTRY CELL, A1, with the formulas I
> posted being in A2 and A3, gives the following results:
>
> A1: =DATE(2006,2,28)
> A2: 31-Jan-2006
> A3: 31-Mar-2006
>
> And
>
> A1: =DATE(2006,9,29)
> A2: 31-Aug-2006
> A3: 31-Oct-2006
>
>
> --ron
>

On Thu, 23 Mar 2006 15:09:02 -0800, Tony <[email protected]>
wrote:

>Thank you so much Ron & Harlan
>
>I got a little bit problem with this "=A2+63-DAY(A2+63) (End of month in
>month after Some Date)", because I don't know how many month(s) before or
>after I need to calcaulate until my boss will ask me. if if has me 3 months
>after then I have to get the result at once, but from your formula I have to
>change the no. of days to calculate. I will try to use the eomonth formula,
>but meanwhile it doesn't work with my computer. I don't understand.
>
>Thank you so much for great help.
>Tony
>
>

If the number of months after "some date" is a variable, then use this formula
instead:

A1: SomeDate
B1: Number of Months after the month of SomeDate for A3

A2: =A1-DAY(A1) (end of month prior to some date)
A3: =DATE(YEAR(A2),MONTH(A1)+1+B1,0) (end of month that is B1 months after
the month of SomeDate)


--ron

Just FYI at this point, but for EOMONTH, the analysis toolpak has to be
installed, then enabled through the Tools/Add-Ins menu.

Harlan's formula should have worked fine for you and can adapt easily to
different numbers of months

> > >=DATE(YEAR(x),MONTH(x)+1+n,0)

Where x is the cell containing the first date, and n is the number of months
before or after the first date. Set up another cell for the number of months
and just refer to that cell in the formula.



"Tony" wrote:

> Thank you so much Ron & Harlan
>
> I got a little bit problem with this "=A2+63-DAY(A2+63) (End of month in
> month after Some Date)", because I don't know how many month(s) before or
> after I need to calcaulate until my boss will ask me. if if has me 3 months
> after then I have to get the result at once, but from your formula I have to
> change the no. of days to calculate. I will try to use the eomonth formula,
> but meanwhile it doesn't work with my computer. I don't understand.
>
> Thank you so much for great help.
> Tony
>
>
>
> "Ron Rosenfeld" wrote:
>
> > On 22 Mar 2006 22:32:55 -0800, "Harlan Grove" <[email protected]> wrote:
> >
> > >Ron Rosenfeld wrote...
> > >...
> > >>A1: Some Date
> > >>A2: =A1-DAY(A1) (End of month prior to Some Date)
> > >>A3: =A2+63-DAY(A2+63) (End of month in month after Some Date)
> > >
> > >February is the curse of all such simplifications. Put =DATE(2006,2,28)
> > >in A2. A3 then returns 30-Apr-2006. Put =DATE(2006,9,29) and
> > >=DATE(2006,8,30) in A2, and see that it's not just February.
> > >
> > >The most reliable way to do this with built-in functions is
> > >
> > >=DATE(YEAR(A2),MONTH(A2)+2,0)
> > >
> > >and generally the equivalent for EOMONTH(x,n) is
> > >
> > >=DATE(YEAR(x),MONTH(x)+1+n,0)
> >
> > Harlan,
> >
> > Either you're sleepy or I'm missing the point you are trying to make.
> >
> > Clearly if you change one of my formulas, you cannot expect the algorithm to
> > work!
> >
> > However, with regard to your formulas, if you force A2 to be 28 Feb 2006, then,
> > as designed, A1 must have been a date in March, and A3 should, indeed be
> > 30-Apr-2006 per the OP's request.
> >
> > However, if you expect A3 to compute correctly when you are not forcing A2 to
> > be an end of month date, you are not understanding what I posted.
> >
> >
> > Entering your example dates in the DATA ENTRY CELL, A1, with the formulas I
> > posted being in A2 and A3, gives the following results:
> >
> > A1: =DATE(2006,2,28)
> > A2: 31-Jan-2006
> > A3: 31-Mar-2006
> >
> > And
> >
> > A1: =DATE(2006,9,29)
> > A2: 31-Aug-2006
> > A3: 31-Oct-2006
> >
> >
> > --ron
> >

Thank you so much, it works now.

Tony


"JMB" wrote:

> Just FYI at this point, but for EOMONTH, the analysis toolpak has to be
> installed, then enabled through the Tools/Add-Ins menu.
>
> Harlan's formula should have worked fine for you and can adapt easily to
> different numbers of months
>
> > > >=DATE(YEAR(x),MONTH(x)+1+n,0)
>
> Where x is the cell containing the first date, and n is the number of months
> before or after the first date. Set up another cell for the number of months
> and just refer to that cell in the formula.
>
>
>
> "Tony" wrote:
>
> > Thank you so much Ron & Harlan
> >
> > I got a little bit problem with this "=A2+63-DAY(A2+63) (End of month in
> > month after Some Date)", because I don't know how many month(s) before or
> > after I need to calcaulate until my boss will ask me. if if has me 3 months
> > after then I have to get the result at once, but from your formula I have to
> > change the no. of days to calculate. I will try to use the eomonth formula,
> > but meanwhile it doesn't work with my computer. I don't understand.
> >
> > Thank you so much for great help.
> > Tony
> >
> >
> >
> > "Ron Rosenfeld" wrote:
> >
> > > On 22 Mar 2006 22:32:55 -0800, "Harlan Grove" <[email protected]> wrote:
> > >
> > > >Ron Rosenfeld wrote...
> > > >...
> > > >>A1: Some Date
> > > >>A2: =A1-DAY(A1) (End of month prior to Some Date)
> > > >>A3: =A2+63-DAY(A2+63) (End of month in month after Some Date)
> > > >
> > > >February is the curse of all such simplifications. Put =DATE(2006,2,28)
> > > >in A2. A3 then returns 30-Apr-2006. Put =DATE(2006,9,29) and
> > > >=DATE(2006,8,30) in A2, and see that it's not just February.
> > > >
> > > >The most reliable way to do this with built-in functions is
> > > >
> > > >=DATE(YEAR(A2),MONTH(A2)+2,0)
> > > >
> > > >and generally the equivalent for EOMONTH(x,n) is
> > > >
> > > >=DATE(YEAR(x),MONTH(x)+1+n,0)
> > >
> > > Harlan,
> > >
> > > Either you're sleepy or I'm missing the point you are trying to make.
> > >
> > > Clearly if you change one of my formulas, you cannot expect the algorithm to
> > > work!
> > >
> > > However, with regard to your formulas, if you force A2 to be 28 Feb 2006, then,
> > > as designed, A1 must have been a date in March, and A3 should, indeed be
> > > 30-Apr-2006 per the OP's request.
> > >
> > > However, if you expect A3 to compute correctly when you are not forcing A2 to
> > > be an end of month date, you are not understanding what I posted.
> > >
> > >
> > > Entering your example dates in the DATA ENTRY CELL, A1, with the formulas I
> > > posted being in A2 and A3, gives the following results:
> > >
> > > A1: =DATE(2006,2,28)
> > > A2: 31-Jan-2006
> > > A3: 31-Mar-2006
> > >
> > > And
> > >
> > > A1: =DATE(2006,9,29)
> > > A2: 31-Aug-2006
> > > A3: 31-Oct-2006
> > >
> > >
> > > --ron
> > >