Home 
Search 
Today's Posts 
#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? 
#2




Calculating Anniversary years
Try,
=DATEDIF(A1,$E$1,"y") Where E1 is the evaluation date. Mike "[email protected]" 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 "[email protected]" 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 "[email protected]" 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 "[email protected]" 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") "[email protected]" 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? 
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  
calculating back 4 years  Excel Worksheet Functions  
calculating years/months with YEARFRAC?  Excel Worksheet Functions  
calculating the years  Excel Worksheet Functions  
Calculating Anniversary  Excel Discussion (Misc queries) 