Excel 2002 automated annual date change

[Cheryl]

I need to know if there is anyway to update a date annually, not daily. In
other words, say today is 8/20/06. I need the date to be updated
automatically on 8/20/07, 8/20/08, etc. It would need to be entered into one
cell because I need to use it in a calculation.
--
Thanks,
Cheryl

[Roar]

Cheryl,

Write the first 2 years dates in the worksheet. Mark those 2 cells, and drag
it all down or to the left. Then the annuals will be updated.
--
Cheryl skrev:

> I need to know if there is anyway to update a date annually, not daily. In
> other words, say today is 8/20/06. I need the date to be updated
> automatically on 8/20/07, 8/20/08, etc. It would need to be entered into one
> cell because I need to use it in a calculation.
> --
> Thanks,
> Cheryl

[Cheryl]

Hi Roar,

Thanks for the input but I actually need it in one cell and for that cell to
automatically update on the anniversary of the date. Sort of like a TODAY
function works, but not daily, only annually. I think what you're referring
to would work as a copying action. I'm not even sure what I need to do can
be done in Excel. Hopefully, I'm wrong.

Thanks,
--
Cheryl


"Roar" wrote:

> Cheryl,
>
> Write the first 2 years dates in the worksheet. Mark those 2 cells, and drag
> it all down or to the left. Then the annuals will be updated.
> --
> Cheryl skrev:
>
> > I need to know if there is anyway to update a date annually, not daily. In
> > other words, say today is 8/20/06. I need the date to be updated
> > automatically on 8/20/07, 8/20/08, etc. It would need to be entered into one
> > cell because I need to use it in a calculation.
> > --
> > Thanks,
> > Cheryl

[Roar]

Well, then you may continue as follows,

Make the series of the relevant dates as I described some place in the
worksheet, e.g. from cell A1 to M1

In the one cell, insert the function =LOOKUP(TODAY(),A1:M1)





Cheryl skrev:

> Hi Roar,
>
> Thanks for the input but I actually need it in one cell and for that cell to
> automatically update on the anniversary of the date. Sort of like a TODAY
> function works, but not daily, only annually. I think what you're referring
> to would work as a copying action. I'm not even sure what I need to do can
> be done in Excel. Hopefully, I'm wrong.
>
> Thanks,
> --
> Cheryl
>
>
> "Roar" wrote:
>
> > Cheryl,
> >
> > Write the first 2 years dates in the worksheet. Mark those 2 cells, and drag
> > it all down or to the left. Then the annuals will be updated.
> > --
> > Cheryl skrev:
> >
> > > I need to know if there is anyway to update a date annually, not daily. In
> > > other words, say today is 8/20/06. I need the date to be updated
> > > automatically on 8/20/07, 8/20/08, etc. It would need to be entered into one
> > > cell because I need to use it in a calculation.
> > > --
> > > Thanks,
> > > Cheryl

[broro183]

Hi Cheryl,

I saw your post yesterday & didn't quite solve it but your comment "I'm not even sure what I need to do can be done in Excel. Hopefully, I'm wrong." provided a little extra motivation ;-)
I think I have it now...

Try entering this:
*cell A1: "=today()"
*cell B1: "8/20/2006" (depending on formatting of cell
*cell C1:
=DATE(YEAR($B$1)+DATEDIF($B$1,$A1,"y"),MONTH($B$1),DAY($B$1))

Or to put it all in one cell:
"=DATE(2006+DATEDIF(DATE(2006,8,20),TODAY(),"y"),8,20)"

For background on the Datedif function check out Chip's page:
http://www.cpearson.com/excel/datedif.htm
(he includes links to other Date And Time Related Procedures at the base of the page)


hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

[Cheryl]

Wow! The all-in-one-cell formula did it. I did all kinds of date
manipulations with my computer and it updated correctly each time

Very cool....I'll check out that page on the Datedif function.

Glad the challenge motivated you further ;-)

Thanks much
--
Cheryl


"broro183" wrote:

>
> Hi Cheryl,
>
> I saw your post yesterday & didn't quite solve it but your comment "I'm
> not even sure what I need to do can be done in Excel. Hopefully, I'm
> wrong." provided a little extra motivation ;-)
> I think I have it now...
>
> Try entering this:
> *cell A1: "=today()"
> *cell B1: "8/20/2006" (depending on formatting of cell
> *cell C1:
> =DATE(YEAR($B$1)+DATEDIF($B$1,$A1,"y"),MONTH($B$1),DAY($B$1))
>
> Or to put it all in one cell:
> "=DATE(2006+DATEDIF(DATE(2006,8,20),TODAY(),"y"),8,20)"
>
> For background on the Datedif function check out Chip's page:
> http://www.cpearson.com/excel/datedif.htm
> (he includes links to other Date And Time Related Procedures at the
> base of the page)
>
>
> hth
> Rob Brockett
> NZ
> Always learning & the best way to learn is to experience...
>
>
> --
> broro183
> ------------------------------------------------------------------------
> broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
> View this thread: http://www.excelforum.com/showthread...hreadid=573658
>
>

[broro183]

Pleased I could help :-)

As far as I can tell the only thing that will upset the formula is if your date eg 8/20/2006 is greater than the today() result - & this is explained in the link.

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

[Cheryl]

Hi Rob,

You were right. When I tested the formula, it doesn't work if the date you
need inputed is in the future which some of mine are.

Fortunately, I was able to work with it by using the previous anniversary
date, ex 12/17/05, for your formula in column D. In column E, I used the
formula =DATE(YEAR(D5)+1, MONTH(D5), DAY(D5)), which I got from the Web site
you referred me to. That updated correctly, and I was able to do the rest of
my calculations with it. So, it ended up needing 2 cells, instead of 1,
because of the future date problem. Not to bad, I think.

Once again, thanks for your help and if you ever figure out how to get it to
work in one cell, let me know. I've never done very complicated (arrays,
etc) formulas, so this is pretty new to me!
--
Cheryl


"broro183" wrote:

>
> Pleased I could help :-)
>
> As far as I can tell the only thing that will upset the formula is if
> your date eg 8/20/2006 is greater than the today() result - & this is
> explained in the link.
>
> hth
> Rob Brockett
> NZ
> Always learning & the best way to learn is to experience...
>
>
> --
> broro183
> ------------------------------------------------------------------------
> broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
> View this thread: http://www.excelforum.com/showthread...hreadid=573658
>
>