Excel formula years service between years

Hello
The DATEDIF function is useful in this situation, Start Date in A2 and End Date in A3:

Formula:

Please Login or Register  to view this content.

DBY

Hi

Thanks but I need a formula to work out how many years service they have done under the age of 22, years service between 22 and below 41, and years service aged 41+.

Thanks for a quick reply

Apologies this should show:

Start Date of employment: 5/09/1990

Hi All

I have attached an example of the formulas that I need help with.

Highlighted in yellow.

Thanks

The datedif function should help.

You just need to adjust the start and end dates to reflect dates at a persons specific age.

If A1 is a person's birthdate, then their 22nd birthday is
=EDATE(A1,12*22)

So the number of years between your start date (say that's in B1), and their 22nd birthday is
=DATEDIF(B1,EDATE(A1,12*22),"y")


Hope that helps.

Hi Jonmo1

Thanks, but in the spreadsheet, if the person started work at the age of 16, then the services below 22 should give me 5 etc. However I am not getting this formula to work in the spreadsheet when the dates are changes.



Thanks

Adjusting for your actual references,
Formula in E12 is
=DATEDIF(C9,EDATE(C8,12*22),"y") - I get a result of 6
Start Date (C9) is 5 Sep 1975
Birth Date (C8) is 1 Jan 1960 (so 22nd birthday is 1 Jan 1982)
There are 6 years between 5 Sep 1975 and 1 Jan 1982 (76 77 78 79 80 81)

Thanks Jonmo1

How about the "Years service aged 22 + & below 41". The number of years they have worked between the age of 22 and below 41?

Thanks you so much for your help

Just change the 22 to 41 to get the 41st birthday.
That will give all years between start and 41
Then subtract the result of the first formula for below 22.
And for the over 41, use TODAY() instead of the EDATE, and subtract both the first 2 formulas.

See attached for sample formulae which you should be able to transfer to your file.

All DATEFDIF as per Jonmo1's formula with EDATE.

For comparison I included calculations using month rather than year (replace "y" with "m")

Might need to incorporate the MIN function onto the END date to account for your Leave date in C10

=DATEDIF(C9,MIN(C10,EDATE(C8,12*22)),"y")

Hi JohnTopley

Thank you for this. However when I enter the following:
DOB: 17/01/1991
Start date: 30/06/2014
end date: 31/12/2015

This gives me "Age >=22 and <=41" = 17, this is incorrect. This should return 1. As this person has worked for 1year only.

Thanks

Hi Jonom1

I am looking for the number of years of service a person has done within the company. Will the MIN function needed?

Thanks

Originally Posted by vba1234

Will the MIN function needed?

Yes
It's purpose is to return whichever date is earlier, the End Date or the Birthdate.
Otherwise, it would include the years that are AFTER the End date, but before the Birthdate.

You're welcome.

Hi Jonmo1

Are you able to work this out in the sample sheet please, as I am getting bit confused.

Sorry

I also added IFERROR to accomodate errors when the 22nd or 41st birthdate is actually before the Start Date.

EFServiceYears.xlsx

Hi Jonmo1

You are a GENIUS!!!!. Thank you so much. Really appreciated for the help.

Thanks

Hi All

Thank you JohnTopley and Jonom1, really appreciated for the help!.

Thanks

You're welcome.