Calculate years of service

I am looking if there is an Excel formula or macro that can calculate the years of service for each employee based on periods of employment and seniority date. In order for a year to be considered as a year of service, an employee must work a complete calendar year (January 1st through December 31st); otherwise, that period of employment (months) do not count toward someone’s years of service. The problem comes in because we have quite a few employees that have been terminated and rehired. Also, once obtaining the years of service, I am using VLOOKUP to obtain the vacation days from information located on the VacationTable tab. However, I can’t figure out the vacation hours, monthly accrual and daily accrual based on the vacation days.
Can anyone help me? I've been stuck for a while and can’t get this to work.
Please refer to attached file for better understanding of what I am trying to accomplish.

This will not answer all you questions, but should give you a start to getting the answers you need.

In order for a year to be considered as a year of service, an employee must work a complete calendar year (January 1st through December 31st);

This means your Start Date is always Jan 1st of the year after your date of hire, IE

Actual Date of Hire is 09/03/1997
To be considered as years of service, Date is 01/01/1998

It also means your Termination Date, is always Dec 31st of the year prior to your actual Termination Date, IE

Actual Date of Termination is 03/22/2003
To be considered as years of service, Date is 12/31/2002

So a formula like:

=IFERROR(DATEDIF(DATEVALUE("01/01/"&YEAR(D3)+1),DATEVALUE("12/31/"&YEAR(E3)-1),"Y"),0)

Will give you the number of years of service for the Original Hire & 1st Term Date.

The formula can probably be shortened to something like:

=IFERROR(SUM(YEAR(E3)-1)-(YEAR(D3)+1),0) but this is untested.

You will also need to add a check to make sure that there is a Termination Date else the formula does not work.

If there is no Termination Date, then you are still working, so we use Todays Date, with the TODAY() function
something like:

=IF(E3>0,IFERROR(DATEDIF(DATEVALUE("01/01/"&YEAR(D3)+1),DATEVALUE("12/31/"&YEAR(E3)-1),"Y"),0),IFERROR(DATEDIF(DATEVALUE("01/01/"&YEAR(D3)+1),DATEVALUE("12/31/"&YEAR(TODAY())-1),"Y"),0))

This will have to be repeated 4 times, and then you will need to Total all the results.

It also means you will need to modify the above formula with another check, to make sure there is a Hire Date.

So the formula now become something like:

=IF(D3="",0,IF(E3>0,IFERROR(DATEDIF(DATEVALUE("01/01/"&YEAR(D3)+1),DATEVALUE("12/31/"&YEAR(E3)-1),"Y"),0),IFERROR(DATEDIF(DATEVALUE("01/01/"&YEAR(D3)+1),DATEVALUE("12/31/"&YEAR(TODAY())-1),"Y"),0)))

To make life a bit easier I would add a Helper Column between each section, and do the calculation for each section, then total everything.

As JBeaucaire pointed out, you can't use the IFERROR() function, so try this, which seems to work, and is a bit shorter to boot.

=IF(D3="",0,IF(E3>0,IF(SUM(YEAR(E3)-1)-(YEAR(D3)+1)>=1,SUM(YEAR(E3)-1)-(YEAR(D3)+1),0),IF(SUM(YEAR(TODAY())-1)-(YEAR(D3)+1)>=1,SUM(YEAR(TODAY())-1)-(YEAR(D3)+1),0)))

daddylonglegs,

You are correct, it's an artifact of the math: 1998 + 4 = 2002

Have not tried your formula, but this seems to work:

=IF(D3="",0,IF(E3>0,SUM((YEAR(E3)-1)-(YEAR(D3)+1)+1),SUM(YEAR(TODAY())-1)-(YEAR(D3)+1)+1))

Mr. Beaucaire, thanks you for sharing your knowledge with daddylonglegs, xenixman, and me; I appreciate your help with this problem that I am facing. I’d like to take the opportunity to let you know that I am a huge fan of you, and I have used a lot of your work on my everyday life. I feel privileged to have you, daddylonglegs and xenixman here helping me with this problem. However, I don’t understand by what you mean with OP on Excel 2003. I am still learning Excel.

Thank you, Mr. daddylonglegs for your time and sharing your knowledge with me. I used your formula: =MAX(0,SUM(IF(MOD(COLUMN(D3:J3)-COLUMN(D3),2)=0,IF(D3:J3<>"",YEAR(IF(E3:K3,E3:K3,TODAY())+1)-YEAR(D3:J3-1)-1))))

confirmed with CTRL+SHIFT+ENTER
but I am getting a #VALUE error on Ford, Musco's years of service. Shouldn’t I get in return a value of 4 years of service instead?
Again, this forum wouldn’t be the same without you guys.

Thanks Mr. Beaucaire for clarifying it for me. Now that I almost have the years of service, I am using this formula =(N3*8) to get the vacation hours; however, I don't know how would I get the monthly accrual or the daily accrual. Any help would be very appreciating.

I am getting a #VALUE error on Ford, Musco's years of service.

His first TERM DATE: 11/31/2001 is your problem.
There are only 30 days in November.

Sorry for getting back to you late. Just came back from vacations.
Thank you, Xenixman for pointing this out. After fixing the date, the #VALUE went away. Any help would be very appreciating if someone can help with figuring out the daily and monthly accrual. Happy New Year to all you!