Help with what route to go with function

[kasey217]

I have a spreadsheet that I need to calculate a bonus based on the years of service. I have had a formula in the past, but every year it has to be updated because the formula is if the employee has 5-9.9 years of service they get a 2% bonus of their annual salary, if the employee has 10-14.9 they get 3% of their annual salary, and if they have 15 years or more they get 4% of their annual salary. I have tried doing code but I can't figure out what code to do. This is my spread sheet attached with changed names and salaries, but can someone help? Please and Thank you?

Name Title Dept Annual Salary Hire date Current FY Yrs of Service Bonus Pay
Grover Jones Electrician Maintenance 47,741 7/1/2012 7/1/2018 6.00
Bob Baily Assistant Manager Facility Operations 28,500 6/1/2012 7/1/2018 6.08
Kelly White Manager Facility Operations 38,961 5/1/2012 7/1/2018 6.17
Denny James Coordinator Facility Operations 34,735 5/1/2012 7/1/2018 6.17
Nate Johnson HVAC Tech Maintenance 47,741 7/1/2011 7/1/2018 7.01
Tina Brown Manager Facility Operations 44,558 6/1/2011 7/1/2018 7.09
Gary Cole Grounds Worker Maintenance 28,114 6/1/2011 7/1/2018 7.09
Jenny White Custodian Maintenance 28,777 6/1/2011 7/1/2018 7.09
Lisa Miller Custodian Maintenance 29,402 4/1/2011 7/1/2018 7.25
Sara Cleveland Manager Facility Operations 39,865 2/1/2011 7/1/2018 7.42
Katrine Kelly Manager Facility Operations 37,132 11/1/2010 7/1/2018 7.67
Jake Jones Supervisor Maintenance 44,558 9/1/2010 7/1/2018 7.84
Jessica Jone Supervisor Facility Operations 45,619 8/1/2010 7/1/2018 7.92
Chuck Barnes General Maintenance 37,636 8/1/2010 7/1/2018 7.92

 

[Nine Zero]

assuming your data headers begin at A1 and your bonus header is in H1 try this out in H2

=IF(AND(G2>5,G2<9.99),D2*0.2,IF(AND(G2>10,G2<14.99),D2*0.03,IF(G2>15,D2*0.04,"No Bonus")))

 

[kasey217]

assuming your data headers begin at A1 and your bonus header is in H1 try this out in H2

=IF(AND(G2>5,G2<9.99),D2*0.2,IF(AND(G2>10,G2<14.99),D2*0.03,IF(G2>15,D2*0.04,"No Bonus")))

I had to change a few columns in the formula, but you are amazing. I got it. Thank you so much!!!!