Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
next anniversary date
I want to calculate an employee's next anniversary date. If an employee was
hired earlier this year I need the date to reflect next year. Some anniversary dates have already occured this year so changing the year of the hire date to the current year won't work. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
next anniversary date
hi,
I don't understand how adding 1 year to the hire date doesn't calculate the anniversary so why not hire date in a1 and =DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)) Mike "pebbles" wrote: I want to calculate an employee's next anniversary date. If an employee was hired earlier this year I need the date to reflect next year. Some anniversary dates have already occured this year so changing the year of the hire date to the current year won't work. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
next anniversary date
If they were hired several years ago it only adds one year
"Mike H" wrote: hi, I don't understand how adding 1 year to the hire date doesn't calculate the anniversary so why not hire date in a1 and =DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)) Mike "pebbles" wrote: I want to calculate an employee's next anniversary date. If an employee was hired earlier this year I need the date to reflect next year. Some anniversary dates have already occured this year so changing the year of the hire date to the current year won't work. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
next anniversary date
=DATE(YEAR(TODAY())+(TEXT(A1,"mmdd")TEXT(TODAY(), "mmdd")),MONTH(A1),DAY(A1))
-- David Biddulph "pebbles" wrote in message ... If they were hired several years ago it only adds one year "Mike H" wrote: hi, I don't understand how adding 1 year to the hire date doesn't calculate the anniversary so why not hire date in a1 and =DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)) Mike "pebbles" wrote: I want to calculate an employee's next anniversary date. If an employee was hired earlier this year I need the date to reflect next year. Some anniversary dates have already occured this year so changing the year of the hire date to the current year won't work. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
next anniversary date
David,
This formula is close to what I need but it's showing '09 as the next anniversary for dates that haven't passed yet this year, and '08 for dates that have. Am I missing something? For instance, a person hired on 4/1/06, their next anniversary will be in '09, a person hired on 08/12/99, their next anniversary will be in '08. "David Biddulph" wrote: =DATE(YEAR(TODAY())+(TEXT(A1,"mmdd")TEXT(TODAY(), "mmdd")),MONTH(A1),DAY(A1)) -- David Biddulph "pebbles" wrote in message ... If they were hired several years ago it only adds one year "Mike H" wrote: hi, I don't understand how adding 1 year to the hire date doesn't calculate the anniversary so why not hire date in a1 and =DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)) Mike "pebbles" wrote: I want to calculate an employee's next anniversary date. If an employee was hired earlier this year I need the date to reflect next year. Some anniversary dates have already occured this year so changing the year of the hire date to the current year won't work. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
next anniversary date
Yes, it was too late in the day when I was doing that.
Just change the in the formula to < (or <=, depending on how you want to treat anniversaries occurring today). -- David Biddulph "pebbles" wrote in message ... David, This formula is close to what I need but it's showing '09 as the next anniversary for dates that haven't passed yet this year, and '08 for dates that have. Am I missing something? For instance, a person hired on 4/1/06, their next anniversary will be in '09, a person hired on 08/12/99, their next anniversary will be in '08. "David Biddulph" wrote: =DATE(YEAR(TODAY())+(TEXT(A1,"mmdd")TEXT(TODAY(), "mmdd")),MONTH(A1),DAY(A1)) -- David Biddulph "pebbles" wrote in message ... If they were hired several years ago it only adds one year "Mike H" wrote: hi, I don't understand how adding 1 year to the hire date doesn't calculate the anniversary so why not hire date in a1 and =DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)) Mike "pebbles" wrote: I want to calculate an employee's next anniversary date. If an employee was hired earlier this year I need the date to reflect next year. Some anniversary dates have already occured this year so changing the year of the hire date to the current year won't work. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
next anniversary date
Thank you so much!! That's perfect!
"David Biddulph" wrote: Yes, it was too late in the day when I was doing that. Just change the in the formula to < (or <=, depending on how you want to treat anniversaries occurring today). -- David Biddulph "pebbles" wrote in message ... David, This formula is close to what I need but it's showing '09 as the next anniversary for dates that haven't passed yet this year, and '08 for dates that have. Am I missing something? For instance, a person hired on 4/1/06, their next anniversary will be in '09, a person hired on 08/12/99, their next anniversary will be in '08. "David Biddulph" wrote: =DATE(YEAR(TODAY())+(TEXT(A1,"mmdd")TEXT(TODAY(), "mmdd")),MONTH(A1),DAY(A1)) -- David Biddulph "pebbles" wrote in message ... If they were hired several years ago it only adds one year "Mike H" wrote: hi, I don't understand how adding 1 year to the hire date doesn't calculate the anniversary so why not hire date in a1 and =DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)) Mike "pebbles" wrote: I want to calculate an employee's next anniversary date. If an employee was hired earlier this year I need the date to reflect next year. Some anniversary dates have already occured this year so changing the year of the hire date to the current year won't work. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
next anniversary date
You're welcome.
-- David Biddulph "pebbles" wrote in message ... Thank you so much!! That's perfect! "David Biddulph" wrote: Yes, it was too late in the day when I was doing that. Just change the in the formula to < (or <=, depending on how you want to treat anniversaries occurring today). -- David Biddulph "pebbles" wrote in message ... David, This formula is close to what I need but it's showing '09 as the next anniversary for dates that haven't passed yet this year, and '08 for dates that have. Am I missing something? For instance, a person hired on 4/1/06, their next anniversary will be in '09, a person hired on 08/12/99, their next anniversary will be in '08. "David Biddulph" wrote: =DATE(YEAR(TODAY())+(TEXT(A1,"mmdd")TEXT(TODAY(), "mmdd")),MONTH(A1),DAY(A1)) -- David Biddulph "pebbles" wrote in message ... If they were hired several years ago it only adds one year "Mike H" wrote: hi, I don't understand how adding 1 year to the hire date doesn't calculate the anniversary so why not hire date in a1 and =DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)) Mike "pebbles" wrote: I want to calculate an employee's next anniversary date. If an employee was hired earlier this year I need the date to reflect next year. Some anniversary dates have already occured this year so changing the year of the hire date to the current year won't work. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I find age in years from anniversary date (Age funtion)? | Excel Worksheet Functions | |||
Date range selection? Anniversary dates | Excel Worksheet Functions | |||
Anniversary of a date | Excel Discussion (Misc queries) | |||
formula to display anniversary date | Excel Worksheet Functions | |||
How do I get an anniversary date? | Excel Worksheet Functions |