PDA

View Full Version : Solved: Calculating Hours Earned



RonNCmale
04-08-2010, 01:05 AM
:banghead:

Employees earn 7.23 hrs if employed less than two years
Employees earn 8.46 hrs if employed two years but less than 5 years
Employees earn 10.31 hrs if employed five years but less than 10 years

I need a formula for excel that will calculate from their hire date to today's date to have enter their appropriate leave based on years of service.

Bob Phillips
04-08-2010, 01:22 AM
Ron,

What happens if employed more than 10 years? This formula does not account specifically for that

=7.23+(DATEDIF(date_of_hire,TODAY(),"Y")>=2)*1.23+(DATEDIF(date_of_hire,TODAY(),"Y")>=5)*1.85

RonNCmale
04-08-2010, 01:29 AM
the formula continues
10 but less than 15 12.15
15 but less than 20 14.00
20 or more 15.85

RonNCmale
04-08-2010, 01:51 AM
THANKS XLD, KEPT GOING WITH THE FORMULA AND WORKS GREAT. THANKS AGAIN

Bob Phillips
04-08-2010, 01:54 AM
I see. It might be better to reconstruct then.

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