Calculating leave accruals based on time in service

[frostym54]

I am trying to have a formula that calculates time in service based on hire date and todays date and then giving the amount of leave hours per pay period.

0-4yeas = 4hours
5-9years = 5 hours
10+= 6 hours

 

[Dave Patton]

A couple of examples would be very useful.

Excel 2010
	A	B	C	D	E	F
1	Start	Today	Hours		Bracket	Hours
2	5-May-14	21-Sep-18	4		0	4
3	10-Oct-00	21-Sep-18	6		5	5
4					10	6
5	or
6	5-May-14	21-Sep-18	4
7	10-Oct-00	21-Sep-18	6
1b

Cell Formulas
Range	Formula
C2	=LOOKUP(DATEDIF(A2,B2,"y"),$E$2:$F$4)
C3	=LOOKUP(DATEDIF(A3,B3,"y"),$E$2:$F$4)
C6	=LOOKUP(DATEDIF(A6,B6,"y"),{0,4;5,5;10,6})
C7	=LOOKUP(DATEDIF(A7,B7,"y"),{0,4;5,5;10,6})

 

[frostym54]

A couple of examples would be very useful.

Excel 2010

	A	B	C	D	E	F
1	Start	Today	Hours		Bracket	Hours
2	5-May-14	21-Sep-18	4		0	4
3	10-Oct-00	21-Sep-18	6		5	5
4					10	6
5	or
6	5-May-14	21-Sep-18	4
7	10-Oct-00	21-Sep-18	6

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

1b​

Worksheet Formulas Cell Formula C2 =LOOKUP(DATEDIF(A2,B2,"y"),$E$2:$F$4) C3 =LOOKUP(DATEDIF(A3,B3,"y"),$E$2:$F$4) C6 =LOOKUP(DATEDIF(A6,B6,"y"),{0,4;5,5;10,6}) C7 =LOOKUP(DATEDIF(A7,B7,"y"),{0,4;5,5;10,6}) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Works great Thanks.