A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Calculating Anniversary years



 
 
Thread Tools Display Modes
  #1  
Old August 12th 08, 01:50 PM posted to microsoft.public.excel.worksheet.functions
PH@tic[_2_]
external usenet poster
 
Posts: 9
Default 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 1-1-09.

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 1-1-09 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  
Old August 12th 08, 01:57 PM posted to microsoft.public.excel.worksheet.functions
Mike H
external usenet poster
 
Posts: 11,501
Default 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 1-1-09.
>
> 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 1-1-09 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  
Old August 12th 08, 03:14 PM posted to microsoft.public.excel.worksheet.functions
PH@tic[_2_]
external usenet poster
 
Posts: 9
Default Calculating Anniversary years

Thanks Mike, that works. Now, how can in integrate that into what I had
already created: =IF(C3="Active",$O$2-YEAR(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 1-1-09.
> >
> > 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 1-1-09 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  
Old August 12th 08, 03:25 PM posted to microsoft.public.excel.worksheet.functions
Mike H
external usenet poster
 
Posts: 11,501
Default 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$2-YEAR(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 1-1-09.
> > >
> > > 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 1-1-09 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  
Old August 12th 08, 09:25 PM posted to microsoft.public.excel.worksheet.functions
PH@tic[_2_]
external usenet poster
 
Posts: 9
Default 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 1-1-09.
>
> 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 1-1-09 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 04:37 PM
calculating back 4 years Corey Excel Worksheet Functions 1 February 22nd 07 10:07 AM
calculating years/months with YEARFRAC? Stilla Excel Worksheet Functions 4 February 21st 06 08:06 PM
calculating the years DKY Excel Worksheet Functions 6 February 20th 06 08:06 PM
Calculating Anniversary Penny Excel Discussion (Misc queries) 5 October 7th 05 03:31 PM


All times are GMT +1. The time now is 07:10 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.