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
Start	Today	Hours	Bracket	Hours

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]5-May-14[/TD]
[TD="align: right"]21-Sep-18[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]10-Oct-00[/TD]
[TD="align: right"]21-Sep-18[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]or[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5-May-14[/TD]
[TD="align: right"]21-Sep-18[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]10-Oct-00[/TD]
[TD="align: right"]21-Sep-18[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

1b​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=LOOKUP(DATEDIF(A2,B2,"y"),$E$2:$F$4)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C3[/TH]
[TD="align: left"]=LOOKUP(DATEDIF(A3,B3,"y"),$E$2:$F$4)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C6[/TH]
[TD="align: left"]=LOOKUP(DATEDIF(A6,B6,"y"),{0,4;5,5;10,6})[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C7[/TH]
[TD="align: left"]=LOOKUP(DATEDIF(A7,B7,"y"),{0,4;5,5;10,6})[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Works great Thanks.