Month end calculation

G

Guest

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.
 
D

daddylonglegs

If you're always entering the last day of a month in A1 then for th
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 ToolPa
and use

=EOMONTH(A1,-1)

and

=EOMONTH(A1,1
 
R

Ron Rosenfeld

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.
Click to expand...

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
 
H

Harlan Grove

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)
Click to expand...

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)
 
R

Ron Rosenfeld

Ron Rosenfeld wrote...
...

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)
Click to expand...

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
 
H

Harlan Grove

Ron Rosenfeld wrote.......

OK, it's a 2-fold thing. I missed that. Sorry.

So A3 would be the end of the month in the month after A1 rather than
A2. Your formulas work for that. However, two cells to obtain one
result is wasteful. It's still easier to use
=DATE(YEAR(x),MONTH(x)+1+n,0) in one cell to get the same result as
EOMONTH(x,n). The exception is EOMONTH(x,-1), in which case your A2
formula =x-DAY(x) is best.
 
G

Guest

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
 
G

Guest

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
 
R

Ron Rosenfeld

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
Click to expand...

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
 
G

Guest

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

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.
 
G

Guest

Thank you so much, it works now.

Tony


JMB said:
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


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.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to get largest date from a Vlookup 1
Sum value between two dates and copy to new cell 6
Sum amount of days in another coloumn 8
sumproduct with varying column ranges 1
Date lists 3
summing daily data by month 1
VLOOKUP Help 1
possible countif formula? 3

Top