Thanks:  0
Likes:  0

# Thread: problem with the end of the month date "rolling over" to the

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

[ 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 ]

2. =IF(A1<>"",IF(A1
Note: The EOMONTH function is supplied by the Analysis ToolPak add-in which you may need to install and add.

3. 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 ]

4. =IF(A1<>"",IF(A1
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 ]

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

6. 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 ]

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•