DateDif

**Ironman** · 05-08-2009, 10:12 AM

Hello - - I'm trying to wrap my head around this one...maybe it's Friday....!

Anyway, I have a list of employees: Name, Supervisor and Service Date. I'm trying to add a column that would show the Years of Service for a future date range. The future date range would be Quarter 3 - 2009....I'm looking for those that would have a 5 year anniversary, 10 year, 15, 20...and so on...that would fall between July 2009 and Sep 2009...

Any ideas, suggestions would be great! Thanks...and enjoy your weekend!

**NBVC** · 05-08-2009, 10:27 AM

Are you looking to determine who is at 5, who is at 10, etc... or just who is a factor of 5 years anniversary during that period?

**Ironman** · 05-08-2009, 10:47 AM

Hi....Yeah, I would be looking to determine which employees will have their 5 year anniv., or those with a 10 year, or those with 15, 20, 30, 35, 40...etc.. fall between 7/1/2009 and 9/30/2009...make sense?

**NBVC** · 05-08-2009, 11:14 AM

Have a look at the attached to see if this is close to what you need.

I listed the months of interest (all 3 months have to be listed)... note that the first one starts at the last day of the prev. month..because of the way Datedif() works... If you use Jul 1, 2009, then July 1, 2004, doesn't register...

**NBVC** · 05-08-2009, 11:25 AM

If you need to have the answers in one single column, you can add a column as shown in attached and hide the "worker" columns...

**Ironman** · 05-08-2009, 05:04 PM

Hi....I was just logging back in to the forum to make the vary statement, that I was looking for something more in the line of in one column. Thanks for your help.

**daddylonglegs** · 05-08-2009, 07:44 PM

For a single formula approach......

With hire dates in A2 down use this formula in B2 down

=IF(AND(INT((MONTH(A2)+2)/3)=F$1,MOD(D$1-YEAR(A2),5)=0),D$1-YEAR(A2),"")

Where the required year and quarter are shown in D1 and F1, e.g. 2009 and 3 respectively for your example

Only shows the number of years when a "significant anniversary" falls in the period you want.

See attached