Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advance By Month With A CommandButton
Greetings,
I have a worksheet with a date in C10 in mmm/yyyy formant. I made a CommandButton (NextMonth) to advance the date by 1 month. I have a problem, I used +30 to advance which works for the short term. However, I am going back 12 years and getting a lot of errors in the date. Is there anyway to advance by one month at a time? Any help would be appreciated. TIA -Minitman |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advance By Month With A CommandButton
One way:
With ActiveSheet.Range("C10") .Value = DateAdd("m",1,.Value) End With Another way With ActiveSheet.Range("C10") d = .Value .Value = DateSerial(Year(d), Month(d) + 1, Day(d)) End With On Mon, 25 Oct 2004 13:16:35 -0500, Minitman wrote: Greetings, I have a worksheet with a date in C10 in mmm/yyyy formant. I made a CommandButton (NextMonth) to advance the date by 1 month. I have a problem, I used +30 to advance which works for the short term. However, I am going back 12 years and getting a lot of errors in the date. Is there anyway to advance by one month at a time? Any help would be appreciated. TIA -Minitman |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advance By Month With A CommandButton
There is a problem here if the following month has less days (31st Jan + 1
month = 31st Feb, or 2nd Mar), so this formula goes back to the last day of the previous month. Not perfect but =DATE(YEAR(C1),MONTH(C1)+1,MIN(DAY(C1),DAY(DATE(YE AR(C1),MONTH(C1)+2,0)))) -- HTH RP "Minitman" wrote in message ... Greetings, I have a worksheet with a date in C10 in mmm/yyyy formant. I made a CommandButton (NextMonth) to advance the date by 1 month. I have a problem, I used +30 to advance which works for the short term. However, I am going back 12 years and getting a lot of errors in the date. Is there anyway to advance by one month at a time? Any help would be appreciated. TIA -Minitman |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advance By Month With A CommandButton
oops missed the button bit.
-- HTH RP "Myrna Larson" wrote in message ... One way: With ActiveSheet.Range("C10") .Value = DateAdd("m",1,.Value) End With Another way With ActiveSheet.Range("C10") d = .Value .Value = DateSerial(Year(d), Month(d) + 1, Day(d)) End With On Mon, 25 Oct 2004 13:16:35 -0500, Minitman wrote: Greetings, I have a worksheet with a date in C10 in mmm/yyyy formant. I made a CommandButton (NextMonth) to advance the date by 1 month. I have a problem, I used +30 to advance which works for the short term. However, I am going back 12 years and getting a lot of errors in the date. Is there anyway to advance by one month at a time? Any help would be appreciated. TIA -Minitman |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advance By Month With A CommandButton - Solved
Hey Myrna,
This works great, thanks. -Minitman On Mon, 25 Oct 2004 13:53:34 -0500, Myrna Larson wrote: One way: With ActiveSheet.Range("C10") .Value = DateAdd("m",1,.Value) End With Another way With ActiveSheet.Range("C10") d = .Value .Value = DateSerial(Year(d), Month(d) + 1, Day(d)) End With On Mon, 25 Oct 2004 13:16:35 -0500, Minitman wrote: Greetings, I have a worksheet with a date in C10 in mmm/yyyy formant. I made a CommandButton (NextMonth) to advance the date by 1 month. I have a problem, I used +30 to advance which works for the short term. However, I am going back 12 years and getting a lot of errors in the date. Is there anyway to advance by one month at a time? Any help would be appreciated. TIA -Minitman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dates ... advance 1 Month @ a Time | Excel Discussion (Misc queries) | |||
CommandButton | Excel Programming | |||
CommandButton | Excel Programming | |||
CommandButton | Excel Programming | |||
Commandbutton | Excel Programming |