Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increasing cell date by a month
Hi
Running the code below on cell C2 containing today's date of 12-10-2004 changes cell value to 05-10-2004. Running code a second time changes cell C2 value to 05-09- 2004 Dim dt As Date dt = [C2].Value [C2].Value = dt - Day(dt - 5) How do you alter the code to achieve similar changes, but increase the dates value?. i.e. 01-10-2004 to 05-10-2004 run code again to increase by a month to 05-11-2004. TIA Bob C |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increasing cell date by a month
Bob
you could try something like: Range("A1").Value = Evaluate("=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))") Regards Trevor "Robert Christie" wrote in message ... Hi Running the code below on cell C2 containing today's date of 12-10-2004 changes cell value to 05-10-2004. Running code a second time changes cell C2 value to 05-09- 2004 Dim dt As Date dt = [C2].Value [C2].Value = dt - Day(dt - 5) How do you alter the code to achieve similar changes, but increase the dates value?. i.e. 01-10-2004 to 05-10-2004 run code again to increase by a month to 05-11-2004. TIA Bob C |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increasing cell date by a month
Hi Robert,
I think embedding a worksheet formula within a macro may be a bit unwieldly, and you would probably just want to go with the worksheet solution. Here is my solution: As long as you are in the middle of the month you have absolutely no problem with what is a month from a date. But if you want to go from Jan 31, 2004 the last day of the month what would be one month later would it be Feb 29, 2004 or would it be 2 days later than that date. See if this serves your purpose. =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) Since you asked as a programming question the equivalent follows. Both solutions would provide March 2, 2004 for the one month from Jan 31, 2004. [b1] = DATESERIAL(YEAR([a1]),MONTH([a1])+1,DAY([a1])) changing [a1] and [b1] to variable names would look more conventional, but this works for testing. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Robert Christie" wrote in message ... Hi Running the code below on cell C2 containing today's date of 12-10-2004 changes cell value to 05-10-2004. Running code a second time changes cell C2 value to 05-09- 2004 Dim dt As Date dt = [C2].Value [C2].Value = dt - Day(dt - 5) How do you alter the code to achieve similar changes, but increase the dates value?. i.e. 01-10-2004 to 05-10-2004 run code again to increase by a month to 05-11-2004. TIA Bob C |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increasing cell date by a month
Hi Bob,
Try this Dim dt As Date Dim mnth As Long dt = Range("C2").Value mnth = Month(dt) If Day(dt) = 5 Then mnth = mnth + 1 Range("C2").Value = DateSerial(Year(dt), mnth, 5) -- HTH RP "Robert Christie" wrote in message ... Hi Running the code below on cell C2 containing today's date of 12-10-2004 changes cell value to 05-10-2004. Running code a second time changes cell C2 value to 05-09- 2004 Dim dt As Date dt = [C2].Value [C2].Value = dt - Day(dt - 5) How do you alter the code to achieve similar changes, but increase the dates value?. i.e. 01-10-2004 to 05-10-2004 run code again to increase by a month to 05-11-2004. TIA Bob C |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increasing cell date by a month
Hi Bob
Thankyou for your reply, it works AOK. I must start typing dates when posting as 05-Oct-2004. I feel Trevor and David missed the 5th day requirement of my post due to this, my mistake. Thanks again regards Bob C PS. Bob has "microsoft.public.excel.programming" closed down? I am replying to you via Google Post a Message. I am receiving the "can not connect to server" meassage for the last 20 hrs. "Bob Phillips" wrote in message ... Hi Bob, Try this Dim dt As Date Dim mnth As Long dt = Range("C2").Value mnth = Month(dt) If Day(dt) = 5 Then mnth = mnth + 1 Range("C2").Value = DateSerial(Year(dt), mnth, 5) -- HTH RP "Robert Christie" wrote in message ... Hi Running the code below on cell C2 containing today's date of 12-10-2004 changes cell value to 05-10-2004. Running code a second time changes cell C2 value to 05-09- 2004 Dim dt As Date dt = [C2].Value [C2].Value = dt - Day(dt - 5) How do you alter the code to achieve similar changes, but increase the dates value?. i.e. 01-10-2004 to 05-10-2004 run code again to increase by a month to 05-11-2004. TIA Bob C |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increasing cell date by a month
Bob,
I haven't found a problem with the group, it's been ok for me. I found your explanation fine<g. Shame my solution was more elegant like the backwards version. Bob "Robert Christie" wrote in message om... Hi Bob Thankyou for your reply, it works AOK. I must start typing dates when posting as 05-Oct-2004. I feel Trevor and David missed the 5th day requirement of my post due to this, my mistake. Thanks again regards Bob C PS. Bob has "microsoft.public.excel.programming" closed down? I am replying to you via Google Post a Message. I am receiving the "can not connect to server" meassage for the last 20 hrs. "Bob Phillips" wrote in message ... Hi Bob, Try this Dim dt As Date Dim mnth As Long dt = Range("C2").Value mnth = Month(dt) If Day(dt) = 5 Then mnth = mnth + 1 Range("C2").Value = DateSerial(Year(dt), mnth, 5) -- HTH RP "Robert Christie" wrote in message ... Hi Running the code below on cell C2 containing today's date of 12-10-2004 changes cell value to 05-10-2004. Running code a second time changes cell C2 value to 05-09- 2004 Dim dt As Date dt = [C2].Value [C2].Value = dt - Day(dt - 5) How do you alter the code to achieve similar changes, but increase the dates value?. i.e. 01-10-2004 to 05-10-2004 run code again to increase by a month to 05-11-2004. TIA Bob C |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Increasing Month only in formula | Excel Discussion (Misc queries) | |||
Date arithmetic: adding 1 month to prior end of month date | Excel Worksheet Functions | |||
Sort month/date/year data using month and date only | Excel Discussion (Misc queries) | |||
How can i change cell colour depending on month of date in cell? | Excel Discussion (Misc queries) | |||
Calculate month-end date from date in adjacent cell? | Excel Worksheet Functions |