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.

 Calculating Anniversary years
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## Calculating Anniversary years

#1
August 12th 08, 01:50 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster Posts: 9
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?
#2
August 12th 08, 01:57 PM posted to microsoft.public.excel.worksheet.functions
 Mike H external usenet poster Posts: 11,501
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 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
August 12th 08, 03:14 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster Posts: 9
Calculating Anniversary years

Thanks Mike, that works. Now, how can in integrate that into what I had
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 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
August 12th 08, 03:25 PM posted to microsoft.public.excel.worksheet.functions
 Mike H external usenet poster Posts: 11,501
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
> 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 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
August 12th 08, 09:25 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster Posts: 9
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 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 Linear Mode

 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 User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 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

All times are GMT +1. The time now is 01:04 AM.