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.
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.
Is there a simpler way to do this? Or perhaps some built in function?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...
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?
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
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.
Assuming your date is in cell A1:
Or:VB:=EDATE(A1,1) 'To increase by exactly 1 month
VB:=EDATE(A1,-1) 'To decrease by exactly 1 month
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
There are currently 1 users browsing this thread. (0 members and 1 guests)