Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: Add One Month To Date

  1. #1
    Join Date
    8th July 2006
    Location
    San Diego
    Posts
    215

    Add One Month To Date

    Ok I think this is a really silly problem... Is there a way to add 1 month minus 1 day to a given date? With the exceptions below:

    Let d m and y be the day month and year respectively.

    VB:
    new_m = m+1 
    new_d = d-1 
     
     'EXCEPTIONS:
    If m = 12 Then new_m = 1 And new_y = y + 1 
    If d = 1 Then 
        new_d = 31 
        If m = 4,6,9,11 Then new_d = 30 
        If m = 2 And y mod 4 = 0 Then new_d = 29 
        If m = 2 And y mod 4 <> 0 Then new_d = 28 
    End If 
    If d = 31 Then 
        new_d = 30 
        If m = 4,6,9,11 Then new_d = 29 
         'etc etc
         'then new_d is 28 and 27 for february, and then i have to make exceptino cases for when d is 28 29 30 as well...
    
    
    Is there a simpler way to do this? Or perhaps some built in function?

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th September 2005
    Location
    Hampshire, UK
    Posts
    1,278

    Re: Adding Exactly One Month To A Date

    Hi

    If you set a reference to atpvbaen.xls (ie the Analysis Toolpak) in the VBE via Tools>References then you can use the Edate function.

    Richard

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    23rd April 2008
    Location
    Manchester, UK
    Posts
    12

    Re: Adding Exactly One Month To A Date

    If you just want a formula, this one works for me:

    =TEXT(DATE(YEAR(A1),MONTH(A1)-1,DAY(A1)-1),"dd/mm/yyyy")

    where the original date is in cell A1 and the format required is "dd/mm/yyyy".

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,709

    Re: Adding Exactly One Month To A Date

    Exactly One Month
    Define "Exactly One Month". 31, 30, 29 or 28 days?

  5. #5
    Join Date
    8th July 2006
    Location
    San Diego
    Posts
    215

    Re: Add One Month To Date

    I suppose that is part of my problem, defining "exactly one month"
    In general, if I start on any given day, then the "month" will last until that day - 1.
    So if i start on 1/13 then the end of that "month" will be 2/12.

    However, if i started say on 1/30 then I want the "months" to be as followed:
    1/30-2/27 (assuming no leap year), 2/28-3/29, 3/30-4/29, etc.
    If i started on 1/31, then:
    1/31-2/27, 2/28-3/30, 3/31-4/29, 4/30-5/30, etc.

    I assume this really doesn't make much sense... so I just ended up coding lots of if/then statements to get all the exceptions cases the way i needed it to be. It actually wasn't as much as I thought it would be... but if there is some built in function that does this (assuming people could actually follow my logic on what a "month" is o.O) i'd still be curious to know!

    Formula by peanut gives 3/1 when adding 1 month minus 1 day to 1/31.
    RichardSchollar - I don't have "atpvbaen.xls (ie the Analysis Toolpak)" where is that from?

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Six Strings Guest

    Re: Add One Month To Date

    Your algorithm is not well defined on paper, so your problem is not a coding problem. From what I can gather in your examples, you want this:

    If the current day minus one is available in the following month, use that day
    ' example: 1/15 to 2/14, 1/29 to 2/28

    Else count the number of days remaining in the month after the given day and add 1, and subtract that number from the last day in the following month (last day of the month must take into account leap year for Feb)
    ' examples 1/31 to 2/27 (not leap year), 2/28 to 3/30 (not leap year), 4/30 to 5/30

    End If

    However, note that using this description, you have 1/29 to 2/28, but starting on a later date of 1/31 lands you on an earlier date of 2/27 because you're now counting from the end of the month. This may be an unintended consequence, and I can't tell if you want that to happen, or not.

    My suggestion is that you first write a concise English (or whatever) description of what you want. Once that is clear in your head, it will be easy to code it.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    22nd November 2004
    Location
    Perth - Australia
    Posts
    75

    Re: Add One Month To Date

    Assuming your date is in cell A1:
    VB:
    =EDATE(A1,1) 'To increase by exactly 1 month
    
    
    Or:
    VB:
    =EDATE(A1,-1) 'To decrease by exactly 1 month
    
    
    Cheers,

    Matthew

  8. #8
    Join Date
    29th November 2010
    Posts
    1

    Re: Add One Month To Date

    Apologize if this bumps an old thread. I have nothing to add.

    But perhaps someone more experienced can tell me how I otherwise can say thank you to Mattheq for this tip. Simple, but it worked, and it was important for me to have it solved.
    Last edited by AAE; November 29th, 2010 at 20:33. Reason: delete quote

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    Re: Add One Month To Date

    Hello MikBed,

    Welcome to Ozgrid.

    You could send a Private Message.
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Increase Date By Month When It Equals Current Date
    By RandomIrishGuy in forum EXCEL HELP
    Replies: 2
    Last Post: August 23rd, 2008, 05:19
  2. Increase Date By 1 Month & Return Month Name
    By Macropheliac in forum EXCEL HELP
    Replies: 6
    Last Post: October 29th, 2006, 09:08
  3. Count Date Cells Where Date Is Previous Month
    By tabman in forum EXCEL HELP
    Replies: 18
    Last Post: October 26th, 2006, 04:16
  4. Determine begin month date from month end date.
    By mikeburg in forum EXCEL HELP
    Replies: 3
    Last Post: January 14th, 2006, 04:43
  5. Add a Month to Date
    By 4508Ginx in forum EXCEL HELP
    Replies: 5
    Last Post: September 25th, 2005, 11:10

Bookmarks

Posting Permissions

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