How do I get a date in the past (column a) to show as a due date this year in
column b? i.e. if they joined the company on 3-dec-98 then I want the next
column to automatically say that their appraisal is due on 3-dec-07. THANKS

Try this:

A1: (Hire date..no future dates, no text)

Next anniversary date:
=IF(A1,TODAY()+LOOKUP(365,--(TEXT(A1,"dd-mmm-")&(YEAR(NOW())+{0,1}))-TODAY()),"")

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"Vicki" <(E-Mail Removed)> wrote in message
news:B7DE49FA-F4E4-4C31-9BD7-(E-Mail Removed)...
> How do I get a date in the past (column a) to show as a due date this year
> in
> column b? i.e. if they joined the company on 3-dec-98 then I want the
> next
> column to automatically say that their appraisal is due on 3-dec-07.
> THANKS

You superstar - I would never have got there on my own!

"Ron Coderre" wrote:

> Try this:
>
> A1: (Hire date..no future dates, no text)
>
> Next anniversary date:
> =IF(A1,TODAY()+LOOKUP(365,--(TEXT(A1,"dd-mmm-")&(YEAR(NOW())+{0,1}))-TODAY()),"")
>
> Does that help?
> --------------------------
>
> Regards,
>
> Ron (XL2003, Win XP)
> Microsoft MVP (Excel)
>
> "Vicki" <(E-Mail Removed)> wrote in message
> news:B7DE49FA-F4E4-4C31-9BD7-(E-Mail Removed)...
> > How do I get a date in the past (column a) to show as a due date this year
> > in
> > column b? i.e. if they joined the company on 3-dec-98 then I want the
> > next
> > column to automatically say that their appraisal is due on 3-dec-07.
> > THANKS
>
>
>

=DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))
--
David Biddulph

"Vicki" <(E-Mail Removed)> wrote in message
news:B7DE49FA-F4E4-4C31-9BD7-(E-Mail Removed)...
> How do I get a date in the past (column a) to show as a due date this year
> in
> column b? i.e. if they joined the company on 3-dec-98 then I want the
> next
> column to automatically say that their appraisal is due on 3-dec-07.
> THANKS

Actually, I was close....but the formula I posted trips up if the hire date
is 29-Feb of a leap year. It returns an error if the next anniversary date
is not in a leap year.

To return the same values that the EDATE function would return...(29-Feb
becomes 28-Feb in non-leap years...and 29-Feb in leap years)
try this:
=MIN(DATE(YEAR(NOW())+(MONTH(A1)<MONTH(NOW())),MONTH(A1)+{0,1},DAY(A1)*{1,0}))

--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"Vicki" <(E-Mail Removed)> wrote in message
news:E5ACE6C5-3403-4C36-A58B-(E-Mail Removed)...
> You superstar - I would never have got there on my own!
>
> "Ron Coderre" wrote:
>
>> Try this:
>>
>> A1: (Hire date..no future dates, no text)
>>
>> Next anniversary date:
>> =IF(A1,TODAY()+LOOKUP(365,--(TEXT(A1,"dd-mmm-")&(YEAR(NOW())+{0,1}))-TODAY()),"")
>>
>> Does that help?
>> --------------------------
>>
>> Regards,
>>
>> Ron (XL2003, Win XP)
>> Microsoft MVP (Excel)
>>
>> "Vicki" <(E-Mail Removed)> wrote in message
>> news:B7DE49FA-F4E4-4C31-9BD7-(E-Mail Removed)...
>> > How do I get a date in the past (column a) to show as a due date this
>> > year
>> > in
>> > column b? i.e. if they joined the company on 3-dec-98 then I want the
>> > next
>> > column to automatically say that their appraisal is due on 3-dec-07.
>> > THANKS
>>
>>
>>