Fill in months back automatically - Page 2
Fill in months back automatically
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 34

Thread: Fill in months back automatically

  1. #11

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

    Default

     
    On 2002-03-28 23:51, Bob Umlas wrote:
    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.

    Or, if the date in N1 is changed often and you don't want to drag the cells each time,
    this formula should do it (there could well be a simpler one) :-

    =IF(N1=EOMONTH(N1,0),EOMONTH(EDATE(N1,-1),0),EDATE(N1,-1))


    [ This Message was edited by: gypo on 2002-03-29 00:08 ]

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

    Default

    Hi gypo

    It really depends on what you are determining as 1 month.

    To use you eaxmple of:
    30 November 2002

    If you take 31 days you get
    30/10/02 (same as EDATE)
    If you take 30 days
    you get 31/10/02 (same as what you say)

    To put this another way, if you had
    28/02/2001 in a cell and used the last day of the previous month (31/1/02) as being one month before, you are taking away 28 days, which means you are taking off the number of days for Feb and not really one month.

    However, rather than just nit pick I will offer and alternative:

    =IF(EOMONTH(A1,0)=A1,EOMONTH(A1,-1),EDATE(A1,-1))




  3. #13

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

    Default

    On 2002-03-29 00:41, Dave Hawley wrote:
    Hi gypo

    It really depends on what you are determining as 1 month.

    To use you eaxmple of:
    30 November 2002

    If you take 31 days you get
    30/10/02 (same as EDATE)
    If you take 30 days
    you get 31/10/02 (same as what you say)

    To put this another way, if you had
    28/02/2001 in a cell and used the last day of the previous month (31/1/02) as being one month before, you are taking away 28 days, which means you are taking off the number of days for Feb and not really one month.

    However, rather than just nit pick I will offer and alternative:

    =IF(EOMONTH(A1,0)=A1,EOMONTH(A1,-1),EDATE(A1,-1))

    Yes, that formula is a bit better.

    Didn't think much of your arguments about what constitutes a month, though. One month is not a fixed number of days as you well know.

    It seems to me that an end of a month is the last day of that month - can't really see how it can be something else.


  4. #14
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This one easy...

    enter in reqd cell the date and right click on bottom dright change to cross hold and drag on this now cross (filler handle) whatever its called. and drag and let go.

    Box pops uo select as reqd.

    suggest you choose as reqd loads to pick

    no formulas or remember to do, its aleady there!



    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  5. #15

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

    Default

    On 2002-03-29 05:33, Jack in the UK wrote:
    This one easy...

    enter in reqd cell the date and right click on bottom dright change to cross hold and drag on this now cross (filler handle) whatever its called. and drag and let go.

    Box pops uo select as reqd.

    suggest you choose as reqd loads to pick

    no formulas or remember to do, its aleady there!



    There's an echo on this board :-

    On 2002-03-28 23:51, Bob Umlas wrote:
    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.



    Gypo replied :-

    Or, if the date in N1 is changed often and you don't want to drag the cells each time,
    this formula should do it (there could well be a simpler one) :-

    =IF(N1=EOMONTH(N1,0),EOMONTH(EDATE(N1,-1),0),EDATE(N1,-1))


    [ This Message was edited by: gypo on 2002-03-29 00:08 ]

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

    Default

    Gypo

    RE: Didn't think much of your arguments about what constitutes a month, though.

    Then offer an alternative argument.


    RE: One month is not a fixed number of days as you well know.

    I don't recall ever saying it was.

    RE: It seems to me that an end of a month is the last day of that month

    Well of course it is, but what's your point. I think you are getting confused. The idea is to take 'one month' from consecutive dates, not automatically go to the last day of the preceeding month if the beginning month is the last day of that month.


    RE: can't really see how it can be something else.

    You really have to other some sort of logic for this line of thinking, you cannot just say, 'well, it just is'

    When we put a date in a cell and take 'one month' off that date the 'month'we are going to take must be x number of days. If adding one month to say 31/Oct/02 using EDATE we get 30/NoV/02 , it has added 30 days (the number of days in Nov. the resulting month).

    If we now put 30/Nov/02 in a cell and use EDATE to return a date one month BEFORE this date we get 30/Oct/02 , it has taken 31 days (the number of days in Oct. The resulting month again)

    This same trend continues until you hit 30/Mar/02, the preceeding month is Jan (which only has 28 days) so EDATE can not use this resulting month because this would result in 2/Mar/02 and hence uses 30 days.

    So as you can see I am certainly not saying "a month is a fixed number of days".

    jonasmckee, may well want to always get the last day of the preceeding month if the beggining month is the last day of that month (hence my alternative), but technically this isn't returning the date one month preceeding consecutive months.





    _________________
    Kind Regards
    Dave Hawley
    OzGrid Business Applications
    Microsoft Excel/VBA Training


    [ This Message was edited by: Dave Hawley on 2002-03-29 15:10 ]

  7. #17
    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 Bob Umlas:
    Works Great! and no problem with the number of days in the respective month etc. either.

  8. #18

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

    Default

    Dave Hawley

    I still don't think much of your arguments.

    It's really a matter of answering one simple question :-

    If one month is added to 28 Feb, what result would any normal person expect?

    I think the expected result would be 31 Mar.
    Similarly, for other months where there are differences in the last day.

    If you think some other result should be expected, then that's fine with me.

  9. #19
    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 and Gypo:
    How about if I suggest that each Function, like every tool has it s own special use. Some tools are better suited for some tasks than others. I am sure EDATE, EOMONTH, and EDIT|FILL|SERIES|... have their own place!

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

    Default

      
    gypo, you may not think much of my argument (well it's microsofts really) but at least I am offering one.

    If you also use the Fill handle method and choose- Series - Month -Step value -1 you will also get 30/Oct/2002 if the starting month is 30/Nov/02 (in other words the same logic applies), but after this you are taking 2 months from 30/Oct/02 then 3 etc. This is DIFFERENT to always taking one month off the preceding Month like the in the EDATE eaxmples.


    No offence intended Gypo, but I suggest you contact Microsoft and frustate them with your heart felt argument.

    _________________
    Kind Regards
    Dave Hawley
    OzGrid Business Applications
    Microsoft Excel/VBA Training


    [ This Message was edited by: Dave Hawley on 2002-03-29 16:04 ]

    [ This Message was edited by: Dave Hawley on 2002-03-29 16:13 ]

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