problem with the end of the month date "rolling over" to the
problem with the end of the month date "rolling over" to the
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

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

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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 ]

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =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. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,329
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    =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. #5
    New Member
    Join Date
    Apr 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com