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

PerfectTommy

New Member
Joined
Apr 8, 2002
Messages
15
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
=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.
 
Upvote 0
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
 
Upvote 0
=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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top