Hello,

On worksheet2 B2 I have a date formated like 1/1/2006. I need to change
that value in yearly increments 1/1/2006, 1/1/2007, 1/1/2008 and so on.
How can the date be changed in yearly increments?

Thanks for your help in advance,
jfcby

You could use a formula like:

=DATE(YEAR(B2)+ROWS($B$1:$B1),MONTH(B2),DAY(B2))

in the worksheet itself.

The VBA function DateSerial works in much the same way as the worksheet
function Date, if you want a programmatical solution.

Hope this helps!

Richard



jfcby wrote:
> Hello,
>
> On worksheet2 B2 I have a date formated like 1/1/2006. I need to change
> that value in yearly increments 1/1/2006, 1/1/2007, 1/1/2008 and so on.
> How can the date be changed in yearly increments?
>
> Thanks for your help in advance,
> jfcby

Assume date 1/1/2006 in A1

Load the Analysis Toolpak from Tools>Add-ins then in A2 enter

=EDATE(A1,12)

Drag>copy down as far as you wish.


Gord Dibben MS Excel MVP

On 10 Dec 2006 12:22:30 -0800, "jfcby" <(E-Mail Removed)> wrote:

>Hello,
>
>On worksheet2 B2 I have a date formated like 1/1/2006. I need to change
>that value in yearly increments 1/1/2006, 1/1/2007, 1/1/2008 and so on.
>How can the date be changed in yearly increments?
>
>Thanks for your help in advance,
>jfcby

You can just enter the first two dates, select them, and then drag and copy
to get more.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"jfcby" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hello,
>
> On worksheet2 B2 I have a date formated like 1/1/2006. I need to change
> that value in yearly increments 1/1/2006, 1/1/2007, 1/1/2008 and so on.
> How can the date be changed in yearly increments?
>
> Thanks for your help in advance,
> jfcby
>

Too easy<g>

Gord

On Sun, 10 Dec 2006 21:22:21 -0000, "Bob Phillips" <(E-Mail Removed)> wrote:

>You can just enter the first two dates, select them, and then drag and copy
>to get more.

Gord Dibben MS Excel MVP

Hello,

Is there a way to use the vba code to change the date on Sheet2 Cell 2
with a spinner button on Sheet6?

Thank you for your help,
jfcby



RichardSchollar wrote:
> You could use a formula like:
>
> =DATE(YEAR(B2)+ROWS($B$1:$B1),MONTH(B2),DAY(B2))
>
> in the worksheet itself.
>
> The VBA function DateSerial works in much the same way as the worksheet
> function Date, if you want a programmatical solution.
>
> Hope this helps!
>
> Richard
>
>
>
> jfcby wrote:
> > Hello,
> >
> > On worksheet2 B2 I have a date formated like 1/1/2006. I need to change
> > that value in yearly increments 1/1/2006, 1/1/2007, 1/1/2008 and so on.
> > How can the date be changed in yearly increments?
> >
> > Thanks for your help in advance,
> > jfcby

Thank you for all your help

jfcby


jfcby wrote:
> Hello,
>
> Is there a way to use the vba code to change the date on Sheet2 Cell 2
> with a spinner button on Sheet6?
>
> Thank you for your help,
> jfcby
>
>
>
> RichardSchollar wrote:
> > You could use a formula like:
> >
> > =DATE(YEAR(B2)+ROWS($B$1:$B1),MONTH(B2),DAY(B2))
> >
> > in the worksheet itself.
> >
> > The VBA function DateSerial works in much the same way as the worksheet
> > function Date, if you want a programmatical solution.
> >
> > Hope this helps!
> >
> > Richard
> >
> >
> >
> > jfcby wrote:
> > > Hello,
> > >
> > > On worksheet2 B2 I have a date formated like 1/1/2006. I need to change
> > > that value in yearly increments 1/1/2006, 1/1/2007, 1/1/2008 and so on.
> > > How can the date be changed in yearly increments?
> > >
> > > Thanks for your help in advance,
> > > jfcby