Formula to calculate years of service for next year

**Kimston** · 12-13-2012, 05:04 PM

The formula that I currently have in E2, is giving me the number of years served by an employee. Is there another formula that can give me the number of years each employee has served? I would appreciate your help. This is the formula that I have in E2

Please Login or Register  to view this content.

**Fotis1991** · 12-13-2012, 05:09 PM

Do you this formula>.=YEAR(TODAY())-YEAR(E2), in E2?? as you said?

**Kimston** · 12-13-2012, 05:22 PM

Hi Fotis!! Long time no talk to you. Hope you are doing fine. I'm sorry. I meant H2. Senority Date is on E2, Formula is on H2, but it doesn't give me the amount of years served by an employee. I know it should be one more adding to this year's. Let me try to upload the file. Again, thak you for your help.

**daddylonglegs** · 12-13-2012, 05:32 PM

Have you tried DATEDIF?

=DATEDIF(E2,TODAY(),"y")

**Fotis1991** · 12-13-2012, 05:32 PM

Hi to you too!

Yes is a good idea to upload a small sample worksheet explaing the requesr result!

**Kimston** · 12-13-2012, 05:45 PM

Hi daddylonglegs, it has been a long time since the last time I talked to you. I tried your forumla

Please Login or Register  to view this content.

but it gives me the same results.
Happy Holidays!

**daddylonglegs** · 12-13-2012, 05:57 PM

So what results do you expect for the examples in row 2 and 3?

**Kimston** · 12-13-2012, 06:05 PM

I'd like to get the number of years of service for 2013. I know this formula calculates the years of service which for row 2 it gives me 7, but for 2013 should be 8. How I can accomplish that? Also, once I get the years of service, I need to calculate the number of vacation days based on the Vacation Table.

**daddylonglegs** · 12-13-2012, 06:11 PM

Well, the obvious answer is to use your current formula but add 1,

=YEAR(TODAY())-YEAR(E2)+1

.....but that formula only changes value on 1st January, DATEDIF will update on the anniversary date of E2

**Kimston** · 12-13-2012, 06:40 PM

Mr. Daddylonglegs,
Your forumla

Please Login or Register  to view this content.

worked perfectly for right now because I need to start ordering next year service awards plaques. Also, we always celebrate next month service awarded employees.
Thank you very much everyone for your excellent help.