Solved: Calculating Hours Earned

[RonNCmale]

xld gave me this code to calculate earned time based on years of service;
example; employee earns 7.23 hrs if employed less than two years
employee earns 8.46 hrs if employed two years but less than 5 yrs
employee earns 10.31 hrs if employed 5 yrs but less than 10 yrs
employee earns 12.15 hrs if employed 10 but less than 15 yrs
employee earns 14.00 hrs if employed 15 but less than 20 yrs
employee earns 15.85 hrs if employee 20 yrs or more

=LOOKUP(DATEDIF(date_of_hire,TODAY(),"Y"),{0,2,5,10,15,20},{7.23,8.46,10.31 ,12.15,14,15.85})

the code works but gives the earned time in the year, Is their a way to have the earn leave calculate on hire date.
example: employee hired 4/25/05 and is currently earning 8.46 hrs.
On 4/25/10 the employees earn time should advance to 10.31.

Is their any way to acquire this calculation into excel

[Bob Phillips]

Is this what you want Ron?

=LOOKUP(DATEDIF(date_of_hire,DATE(YEAR(TODAY()),MONTH(date_of_hire),DAY(dat e_of_hire)),"Y"),{0,2,5,10,15,20},{7.23,8.46,10.31,12.15,14,15.85})

[RonNCmale]

I tried the formula, but it didn't work. It still showed advanced earned time prior to the anniversary date or hire date. example hire date 4/10/05 showed earning 10.31 in 1/10/10 it should be showing 8.46 until 4/10/10 at which time it would change to 10.31.

[Bob Phillips]

I thought you wanted to calculate the leave on the anniversary of his hire date. What you are now saying is exactly what the original formulka did is it not?

[RonNCmale]

I'm trying to get the formula to do this. let's say employee Al is hired on 4/1/05. Al would earn 7.23 hrs per month until 4/1/07 then he will earn 8.46. Al would earn this until 4/1/10 then he will earn 10.31. Both formula's calculate the earn time in the appropriate year starting in january. I'm wishing to have the earned time advance in the appropriate month (anniversary date-or hire date)

[Bob Phillips]

Sorry, but it s not calculating as of January, it uses TODAY(). I just set the hire date to 9th April 2005 and I get 10.31, if I change it to 10th April 2005 I get 8.46. Isn't that what you want?

[RonNCmale]

yes, I believe you meant on 4/9/10 would give you 8.46 and on 4/10/10 it changed to 10.31. That is exactly what I want it to do if you set the hire date to 4/9/10. If the formula you are using is giving you these results maybe I copied it wrong.

[Bob Phillips]

Doesn't look like it, I copied what you posted in the opneing post of this thread.

[RonNCmale]

maybe I can send a zip file with the xls attached to show you. Give me a little time to try to upload. Thanks

[Bob Phillips]

Okay, I am just off to the shops. Will check when I get back.

[RonNCmale]

Sheet 1 is where you enter "Date Hired" (date is 3/2/05)
I've entered your formula for wk4,wk8,wk12 in the earned box (H19). The earned time should read on wk4 "8.46" in the earned box, wk8 it should change from 8.46 to 10.31 in the earned box because the person's hire date has passed and they have 5 yrs of service.


thanks for helping! Hope this makes sense now.

[Bob Phillips]

=LOOKUP(DATEDIF(Sheet1!B1,I2,"Y"),{0,2,5,10,15,20},{7.23,8.46,10.31,12.15,14,15.85})

[RonNCmale]

woo hooo that did the trick. Thanks xld for the solution and your patients