HI,

i m working as hr. i have created a leave file in excel. I need formula to calculate the leaves balances i.e., to deduct the leaves from opening balance and show the remaining balance in closing balance, on following conditions.

Condition 1:

If employee status (Column E) is confirmed, then employee will get PL - 15 days, CL - 8 days and sick leaves - 7 days.

if employee status is probation then PL,CL and SL is 0 days.

Once i enter the employee status the above mentioned leaves days has to credited in opening balance (column H, I, J).

Condition 2:

employee can take 2 CL leaves in 3 months, if CL leaves is more than 2 days in 3 months then excess leaves should get deducted from PL leaves.

Example: if an employee takes 1 CL leave in Jan then 1 day should be deducted in CL leaves. Then same employee takes 0 CL leaves in Feb and 3 CL leaves in Mar then 1 day has to be deducted in CL leaves and 2 days in PL leaves. If employee takes 2 days CL leaves in APR then leave deduction should happen in CL leaves.

Condition 3:

If there is no CL and SL leaves then leaves should get deducted in PL leaves.

Condition 4:

If PL, CL, SL leaves are Zero or less than zero then negative value should appear in PL closing balance column.