Increasing cell date by a month

  • Thread starter Thread starter Robert Christie
  • Start date Start date
R

Robert Christie

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
 
Bob

you could try something like:

Range("A1").Value = Evaluate("=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))")


Regards

Trevor
 
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.
 
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)
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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" <[email protected]> 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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top