specifying the Countif range meeting a condition

[divi123]

Hi Guys,

I wanted to create an attendance file for automating leave break up into CL & SL, meeting the certain conditions listed below.

1. an employee is eligible for 1 CL credit per month pertaining to Date of joining, whereas attendance is calculated on regular months.
ex. if an employee joins by 15th April 2013, he is eligible for 1 day leave till 15th May 2013, post to which he is eligible for additional 1 day credit for his 2nd month.

during the attendence period for 1st May to 31st may, he has an opening balance of 2days leave. say if he avails 2days leave on 3rd May 2013 and 14th May 2013, 1 day leave should be reduced from the opening balance and 1 day should be reflected in Loss of pay (LOP) and he should have balance 1 day leave as closing balance which could be adjusted in his 2nd month of joining (i.e) for the leaves he avails after 15th May.


In Simple, i required a formula where leaves should be counted from two different range meeting Date of joining as condition.
that is. to count leaves taken before the completion on 1month (from DOJ) and after completion

Sheet attached for the same. Can anybody help in solving my query, Seeking urgent revert on the same.