Totally confused in Leave days & Earned days

[pipsmultan]

Hi,

Confused have put a lot time on below but couldn't find Solved it sadly ;(

I have a Senior hopefully might we can Solve below situation with formula.


Ever employee has number of Paid laves in his account. But those paid leaves can't be availed all at once.
Only allowed or can availed on every 6 present minimum in a week.


For example:s


One employee has Paid Leaves = 5
On Day 0/1/2/3/4/5 present = Zero Leave allowed.
On 6 preset = 1 Paid Leave can be avail not more then that.
likewise:
12 Present = 2 Paid Leaves can avail not more then that.
18 Preset = 3 Paid Leaves can avail not more then that.
So-on, until reaches to total days in a month 30/31.


Considering one thing If 'Full Present' and no leaves -Or- employee don't want to avail any paid leaves from his available leaves bank, then Earn day = only Total number of Present days (can be 30/31 according to month) & If present are Zero then Earned days should be Zero as well.


Is there any possibility to come up with a CODE on above Senior

Sample Excel file can be download here:
https://1drv.ms/x/s!AtjBTkpO-s3ggxrCYvno4xk119ko?e=M4CdUH


Thank you.


Best,

 

[pipsmultan]

Bump no reply
Any idea and help on this.....