Pro Rated Annual leave with different conditions

[ambz123]

Hi I am task to create a excel formula with the condition where by if staff A is a manager he is entitled to the following leave and max cap at 22;

Grade	Position	1st year	2nd - 5 year	6-9 year	10 & above
M	Manager	14	18	21	22

Non Junior Managerial they are entitled to the following condition of leave and map cap at 21:

Grade	Position	1st year	2nd - 5 year	6 - 9 year	10 & abv
JM	Junior Manager	14	16	18	21

Executive are entitled to the following condition of leave max cap at 18:

Grade	Position	1st year	2nd - 5 year	6 -9 year	10 & abv
E1/E2	Executive	10	14	16	18

Would appreciate some help on this.

I used the following formula to get my current accrued leave, however i am facing problem with including the condition based on their job grade.

=ROUND(IF(DATEDIF([@[JOIN DATE]],$D$2,"M")<3,0,IF(DATEDIF([@[JOIN DATE]],$D$2,"Y")<1,([@[Actual Entitlement]]*(DATEDIF([@[JOIN DATE]],$D$2,"M"))/12),MIN(14,[@[Actual Entitlement]]+((DATEDIF([@[JOIN DATE]],$D$2,"M")-12)/12))))/0.5,0)*0.5