Increase date by on year

[Guest]

I have a macro which clears last years information and makes the worksheet
available for a new year. I want the Macro to increase last year's balance
date by one year. How do you get the Macro to increase the date value of the
active cell by one year?

 

[Bob Phillips]

With Activecell
.Value = Date(Year(.Value)+1,Month(.Value),Day(.Value))
End With

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Guest]

Hi Bob, thanks for the reply,
I have entered your code into my macro, but when I run the macro I have a
Compile error come up "Expected" The helpfile is no help to me!

Regards

 

[John]

Bob,

How would this work in a general Excel cell to increase the year & month by
12 months

 

[Susan]

how 'bout

=today()+365

or

=A3+365
(because of 2007's leap year, at this point you have to make it +366).

hth
susan

 

[Conan Kelly]

John,

What this code is doing is setting the value of the active cell (.Value = )
to the current value of the active cell adding one year
(Date(Year(.Value)+1,Month(.Value),Day(.Value))).

Although, if I'm not mistaken, it should be
DateSerial(Year(.Value)+1,Month(.Value),Day(.Value)) in VBA....Date(y,m,d)
in XL.

Lookup DateSerial in the VBA Help. DateSerial() returns a date serial
number and requires 3 arguments: Year, Month & Day in that order.

DateSerial(Year,Month,Day)
DateSerial(2008,1,23) 'Today's date

"ActiveCell.Value = DateSerial(2008,1,23)" would set the value of the active
cell to today's date.

Let's say we store today's date in a variable:

dim pdteToday as date
pdteToday = DateSerial(2008,1,23)

now using the variable, the DateSerial() function, and the Year(), Month(),
& Day() functions, we can set the value of the active cell to 1 year from
today:

ActiveCell.Value = DateSerial(Year(pdteToday) + 1, Month(pdteToday),
Day(pdteToday))

"Year(pdteToday)" extracts the year from pdteToday (2008),
"Month(pdteToday)" extracts the month (1), and "Day(pdteToday)" extracts the
day (23).......so:

ActiveCell.Value = DateSerial(2008 + 1, 1, 23)

Then:

ActiveCell.Value = DateSerial(2009, 1, 23)

Then

ActiveCell.Value = #1/23/2009# 'USA date format

Which would set the active cell to a year from today.

Now lets say that there is already a date in the active cell. I'm going to
use the existing date that is already there and add a year to it. Using the
previous code, I would replace "pdteToday" with "ActiveCell.Value":

ActiveCell.Value = DateSerial(Year(ActiveCell.Value) + 1,
Month(ActiveCell.Value), Day(ActiveCell.Value))

....what is essentially what Bob did, he just simplified it with "With
ActiveCell" and "End With"

HTH,

Conan