If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




Calculating Anniversary years
I need to determine which employees might fall into a particular group of
anniversary dates 1 year, 2 year, 3 year, etc. I have a column with a hire date dd/mm/yyyy and would like to create a column where the anniversary year is the result based on a reference to data in an absolute cell that I could change each month. To start, I need to group people as to where they would be as of 1109. I had it calculated at first when I put 2009 in the absolute cell, but I need to refine it by the month, so my initial thinking is to put 1109 in the absolute cell, then change it each month to recalculate going forward. what forumula can I write to calculate an anniversary year by the method described abvove? 
Ads 
#2




Calculating Anniversary years
Try,
=DATEDIF(A1,$E$1,"y") Where E1 is the evaluation date. Mike "PH@tic" wrote: > I need to determine which employees might fall into a particular group of > anniversary dates 1 year, 2 year, 3 year, etc. > > I have a column with a hire date dd/mm/yyyy and would like to create a > column where the anniversary year is the result based on a reference to data > in an absolute cell that I could change each month. To start, I need to > group people as to where they would be as of 1109. > > I had it calculated at first when I put 2009 in the absolute cell, but I > need to refine it by the month, so my initial thinking is to put 1109 in > the absolute cell, then change it each month to recalculate going forward. > > what forumula can I write to calculate an anniversary year by the method > described abvove? 
#3




Calculating Anniversary years
Thanks Mike, that works. Now, how can in integrate that into what I had
already created: =IF(C3="Active",$O$2YEAR(D3),"none") I had made the previous forumula to review all the records, for the ones that were active employees, calculate an anniversary group otherwide, display none. How can I integrate your solution to also include the other actions: do the calculation for active employees, otherwise display "None." Thanks. "Mike H" wrote: > Try, > > =DATEDIF(A1,$E$1,"y") > > Where E1 is the evaluation date. > > Mike > > "PH@tic" wrote: > > > I need to determine which employees might fall into a particular group of > > anniversary dates 1 year, 2 year, 3 year, etc. > > > > I have a column with a hire date dd/mm/yyyy and would like to create a > > column where the anniversary year is the result based on a reference to data > > in an absolute cell that I could change each month. To start, I need to > > group people as to where they would be as of 1109. > > > > I had it calculated at first when I put 2009 in the absolute cell, but I > > need to refine it by the month, so my initial thinking is to put 1109 in > > the absolute cell, then change it each month to recalculate going forward. > > > > what forumula can I write to calculate an anniversary year by the method > > described abvove? 
#4




Calculating Anniversary years
Try,
=IF(C3="Active",DATEDIF(D3,$O$2,"y"),"none") Mike "PH@tic" wrote: > Thanks Mike, that works. Now, how can in integrate that into what I had > already created: =IF(C3="Active",$O$2YEAR(D3),"none") I had made the > previous forumula to review all the records, for the ones that were active > employees, calculate an anniversary group otherwide, display none. > > How can I integrate your solution to also include the other actions: do the > calculation for active employees, otherwise display "None." > > Thanks. > > "Mike H" wrote: > > > Try, > > > > =DATEDIF(A1,$E$1,"y") > > > > Where E1 is the evaluation date. > > > > Mike > > > > "PH@tic" wrote: > > > > > I need to determine which employees might fall into a particular group of > > > anniversary dates 1 year, 2 year, 3 year, etc. > > > > > > I have a column with a hire date dd/mm/yyyy and would like to create a > > > column where the anniversary year is the result based on a reference to data > > > in an absolute cell that I could change each month. To start, I need to > > > group people as to where they would be as of 1109. > > > > > > I had it calculated at first when I put 2009 in the absolute cell, but I > > > need to refine it by the month, so my initial thinking is to put 1109 in > > > the absolute cell, then change it each month to recalculate going forward. > > > > > > what forumula can I write to calculate an anniversary year by the method > > > described abvove? 
#5




Calculating Anniversary years
I didn't get your post originally, but following is my result: I think with
your help, I got there. I appreciate it! =IF(C3="Active",DATEDIF(D3,$O$2,"y"),"none") "PH@tic" wrote: > I need to determine which employees might fall into a particular group of > anniversary dates 1 year, 2 year, 3 year, etc. > > I have a column with a hire date dd/mm/yyyy and would like to create a > column where the anniversary year is the result based on a reference to data > in an absolute cell that I could change each month. To start, I need to > group people as to where they would be as of 1109. > > I had it calculated at first when I put 2009 in the absolute cell, but I > need to refine it by the month, so my initial thinking is to put 1109 in > the absolute cell, then change it each month to recalculate going forward. > > what forumula can I write to calculate an anniversary year by the method > described abvove? 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
How do I find age in years from anniversary date (Age funtion)?  Enquire  Excel Worksheet Functions  1  January 1st 08 03:37 PM 
calculating back 4 years  Corey  Excel Worksheet Functions  1  February 22nd 07 09:07 AM 
calculating years/months with YEARFRAC?  Stilla  Excel Worksheet Functions  4  February 21st 06 07:06 PM 
calculating the years  DKY  Excel Worksheet Functions  6  February 20th 06 07:06 PM 
Calculating Anniversary  Penny  Excel Discussion (Misc queries)  5  October 7th 05 03:31 PM 