Number based off Years of Service

[tcfd1166]

I am trying to build a function that is based off of years of service by hire date. The hire date cell uses this function : =DATEDIF(B27,$C$5, "y") where B27 is the hire date cell. and $C$5 is the date value we enter to check and update the sheet.

The function needs to do the following:

Years of Service based off Hire Date	Result
0-1	1
1-2	2
2-3	3
3+	4

I have tried multiple IF statements and can only get a return of either 1 or False. I cannot get it to go any further. Any help or suggestions on a formula?

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[tcfd1166]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

365

 

[Fluff]

Thanks for that, how about

=SWITCH(DATEDIF(B27,$C$5, "y"),0,"0-1",1,"0-1",2,"1-2",3,"2-3","3+")

 

[tcfd1166]

Thanks for that, how about

=SWITCH(DATEDIF(B27,$C$5, "y"),0,"0-1",1,"0-1",2,"1-2",3,"2-3","3+")

That pulled the range. My example is in year 2 right now hard coded. It populated 1-2 instead of 2.
I think the function is backwards, I need to show 1,2,3,4 for years instead of 0-1, 1-2, 2-3, 3+. If a date falls in 1-2 years, I need to show 2.

 

[Fluff]

Sorry but I don't understand, your formula will only return whole numbers so it does not return 0-1 or 1-2 etc.

 

[tcfd1166]

If a person has 0-1 years experience, I want a value of "1".
1-2 years experience, value "2"
2-3 years experience, value "3"
3+ years experience, value "4"

The switch function works somewhat.
=SWITCH(C27,0,"1",1,"2",2,"3",3,"4"), but I need to go higher than 3. I could enter in the formula a maximum code I guess of say 50 for the range, (8,"4", 9,"4",10,"4"....) Basically anything after 3 years of service should populate a "4".

 

[Fluff]

Try it like

=SWITCH(DATEDIF(B26,$C$5, "y"),0,1,1,2,2,3,4)

 

[tcfd1166]

This version still shows a value of 3 instead of a 2. Their hire date is in September of 2018, so they should not populate a 3 until September.

=SWITCH(C27,0,1,1,2,2,3,4)

I was trying multiple IF(AND statements to get it to populate in between years, but I could never get it to work either.

 

[Fluff]

Can you post a sample of your data along with the result of your datedif formula & the expected results.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.