Fill in months back automatically
Manage your personal finances in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 34

Thread: Fill in months back automatically

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

    Default

     
    I am need of a way for the cells in row 1 to fill in the dates automatically backwards for 13 months. For example..
    At cell N1 the user would put in a Date (3/15/02), then cells M1 backwards to A1 would then fill in the dates (M1 would equal N1 minus one month, L1 would equal N1 minus one month, etc.)
    Any help would be aprrectiated please..


  2. #2
    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

    in N1 3/15/02 or 15/03/02 depending on how your system is set up (edited)

    in M1 =DATE(YEAR(N1),MONTH(N1)-1,DAY(N1))
    copy M1 to L to A

    Set references to absolute if all months
    are to be - 1.

    Format the cells so the information fits.

    [ This Message was edited by: Dave Patton on 2002-03-28 19:59 ]

    [ This Message was edited by: Dave Patton on 2002-03-28 20:01 ]

  3. #3
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    You can also use the EDATE Function for this. Let's say the date is in cell A1

    =EDATE(A1,-1)

    Just copy this across. The EDATE is part of an Excel Add-in so before trying, go to Tools>Add-ins and check the "Analysis toolpak"



  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-28 19:37, Dave Patton wrote:

    in N1 15/03/02
    in M1 =DATE(YEAR(N1),MONTH(N1)-1,DAY(N1))
    copy M1 to L to A

    Set references to absolute if all months
    are to be - 1.

    Format the cells so the information fits.
    Hi Dave Patton:
    In N1 ... 3/15/02 and not 15/3/02

    Funny thing is Excel will accept an entry like 15/3/02 as a text entry and fool one into thinking every thing is OK!

    [ This Message was edited by: Yogi Anand on 2002-03-28 19:54 ]

    [ This Message was edited by: Yogi Anand on 2002-03-28 19:55 ]

  5. #5
    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


    Quote
    " Funny thing is Excel will accept an entry like 15/3/02 as a text entry and fool one into thinking every thinking is OK!"

    Sorry about that, my system is set ddmmyyyy and my editor is nearly asleep. I should not do my own editing.

    The type does not change the suggestion.

    You have a major challenge if your are about to edit the spelling and English in these newsgroups!

  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Dave Hawley:
    That's Great ... Short and Sweet!

  7. #7

    Join Date
    Feb 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-28 19:48, Dave Hawley wrote:
    Hi

    You can also use the EDATE Function for this. Let's say the date is in cell A1

    =EDATE(A1,-1)

    Just copy this across. The EDATE is part of an Excel Add-in so before trying, go to Tools>Add-ins and check the "Analysis toolpak"

    But, small note :-

    If the date entered in the start cell is the last day of a month and the last day of each month is required in the other cells, EDATE on its own will not produce the required results ..... but then neither will the formula =DATE(YEAR(N1),MONTH(N1)-1,DAY(N1))




    [ This Message was edited by: gypo on 2002-03-28 21:19 ]

  8. #8
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    RE:If the date entered in the start cell is the last day of a month and the last day of each month is required in the other cells, EDATE on its own will not produce the required results

    Maybe you should try it and you will see it does.

  9. #9

    Join Date
    Feb 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-28 22:50, Dave Hawley wrote:
    Hi

    RE:If the date entered in the start cell is the last day of a month and the last day of each month is required in the other cells, EDATE on its own will not produce the required results

    Maybe you should try it and you will see it does.

    I would suggest that you're the one who hasn't tried it.

    Put 30 November 2002 in cell A1.
    Put =EDATE(A1,-1) in A2 and drag down.

    A3 will show 30 Oct (normally the 31st is the last day of October, isn't it?), and the day in each cell below will be 30 until Feb when it will be 28. Thereafter, the day in each cell will be 28.


  10. #10
    Board Regular
    Join Date
    Mar 2002
    Location
    NY
    Posts
    1,041
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    MUCH easier way:
    In N1 enter your date. Then right-click the fill handle and drag left to cell B1, for example. When you let go, you'll see a dropdown which enables you to choose "Fill Months". They will be backwards, as you wanted.

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