Increase date by on year

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pkeegs - 07 Mar 2006 21:59 GMT

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?

Reply to this Message

Bob Phillips - 07 Mar 2006 22:07 GMT

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)

> 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?

Reply to this Message

pkeegs - 08 Mar 2006 21:11 GMT

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

> With Activecell
> .Value = Date(Year(.Value)+1,Month(.Value),Day(.Value))
[quoted text clipped - 13 lines]
> the
> > active cell by one year?

Reply to this Message

John - 23 Jan 2008 19:08 GMT

Bob,

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

> With Activecell
> .Value = Date(Year(.Value)+1,Month(.Value),Day(.Value))
[quoted text clipped - 13 lines]
> the
> > active cell by one year?

Reply to this Message

Susan - 23 Jan 2008 19:18 GMT

how 'bout

=today()+365

or

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

hth
susan

> Bob,
>
[quoted text clipped - 20 lines]
>
> - Show quoted text -

Reply to this Message

Conan Kelly - 23 Jan 2008 19:54 GMT

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

> Bob,
>
[quoted text clipped - 22 lines]
>> the
>> > active cell by one year?

Reply to this Message