problem with the end of the month date "rolling over" to the

[PerfectTommy]

Hello all,

I have a worksheet with 2 columns. Column A has a date (mm/dd/yy). Column B is for a user to enter a dollar amount. In trying to make this as user friendly as possible, I have set up Column A to put in the next day's date when there is an entry in Column B.
(Sorry, the columns won't line up)
Column A Column B
4/1/02 $10.00
4/2/02
When using the worksheet, the user must enter the first date (in cell A1) then the rest of the dates are calculated with a simple formula (=A1+1)copied in the next 30+ cells. This works nicely until the end of the month when the date "rolls over" to the next month.
Column A Column B
4/30/02 $10.00
5/1/02
My question (finally): Is there a way to stop the "roll over" and have the period end at the last day of the month? I realize I will probably have to change the formula used to produce the date, and that is not a problem.
Thanks in advance for your assistance.
This message was edited by PerfectTommy on 2002-04-19 10:08
This message was edited by PerfectTommy on 2002-04-19 10:09
This message was edited by PerfectTommy on 2002-04-19 10:12

 

[Mark W.]

=IF(A1<>"",IF(A1<EOMONTH(A1,0),A1+1,""),"")

Note: The EOMONTH function is supplied by the Analysis ToolPak add-in which you may need to install and add.

 

[IML]

err.... nevermind.

Maybe if I pasted the formula as mark wrote it, I would have been better off. Shoe taste good!

sorry.








another option
=IF(ROW()<=DAY(DATE(YEAR($A$1),MONTH($A$1)+1,0))-DAY($A$1)+1,A1+1,"")

It is uglier, but will eliminate the #value errors that could be caused by mark's when it evaluates on the first "blank"
This message was edited by IML on 2002-04-19 10:35

 

[Dave Patton]

=IF(A1<>"",IF(A1<DATE(YEAR(A1),MONTH(A1)+1,0),A1+1,""),"")

or put the date calculation in say B1 and use
=IF(AND(A1<>"",A1<$B$1),A1+1,"")
This message was edited by Dave Patton on 2002-04-19 10:46

 

[PerfectTommy]

Thank you all for the responses. Thing are working perfectly now.
Thanks to everyone who helped.

 

[Mark W.]

On 2002-04-19 10:30, IML wrote:
err.... nevermind.

Maybe if I pasted the formula as mark wrote it, I would have been better off. Shoe taste good!

sorry.
This message was edited by IML on 2002-04-19 10:35

Yeah, I caught the problem (right after my original posting) when I changed my sample data from April to February.

That Edit button can be a life (err, embarrassment
This message was edited by Mark W. on 2002-04-19 16:01