software

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 amp; A3. My formular is liked quot; =A1-31 , = A1 31. But because of quot;31quot;
has to change each month, therefore if doesn't work to my calcaulation.
Also, from the above example, the calculation for March is correct quot;31-03-06quot;
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)--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=525534=EOMONTH(A1,-1)
=EOMONTH(A1,1)quot;Tonyquot; wrote:

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

the formula doesn't work, it came #NAME? as result. I don't know why.Thanks
Tonyquot;JMBquot; wrote:

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

On Wed, 22 Mar 2006 16:33:30 -0800, Tony gt;
wrote:

gt;I am writing a formula to calculate the last and next month end e.g. if I
gt;enter 28-02-06, the expected result will be liked 31-01-06 and 31-03-06.
gt;28-02-06 will be stored in cell A1, and my expected result will be displayed
gt;in A2 amp; A3. My formular is liked quot; =A1-31 , = A1 31. But because of quot;31quot;
gt;has to change each month, therefore if doesn't work to my calcaulation.
gt;Also, from the above example, the calculation for March is correct quot;31-03-06quot;
gt;but the January is worng, it comes date on 28-01-06. But I need both result
gt;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

quot;Tonyquot; wrote:

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

Ron Rosenfeld wrote...
....
gt;A1: Some Date
gt;A2:=A1-DAY(A1) (End of month prior to Some Date)
gt;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, quot;Harlan Grovequot; gt; wrote:

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

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

Thank you so much Ron amp; Harlan

I got a little bit problem with this quot;=A2 63-DAY(A2 63) (End of month in
month after Some Date)quot;, 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
quot;Ron Rosenfeldquot; wrote:

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