Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
anniversary dates
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
anniversary dates
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
anniversary dates
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
anniversary dates
=DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))
-- David Biddulph "Vicki" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
anniversary dates
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())),MON TH(A1)+{0,1},DAY(A1)*{1,0})) -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Vicki" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date range selection? Anniversary dates | Excel Worksheet Functions | |||
Days to next anniversary | Excel Discussion (Misc queries) | |||
Calculating Anniversary | Excel Discussion (Misc queries) | |||
recurring anniversary dates | Excel Discussion (Misc queries) | |||
How do I get an anniversary date? | Excel Worksheet Functions |