formula for calculating leaves


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.

Book1.xlsx31.98 KB
Nick's picture

 Here's an employee leave

 Here's an employee leave system that we already created... have a look and see if it does what you're looking for: