Need help on Present/Leave and Earned days

Hi,

I am trying to accomplish on below Scenarios of employee present days in Formula on Cell ' I10'. but fail to do so.


Scenario #1
IF Present Days are 6 minimum then Employee are allowed to get Two Paid Leave and this should add +1 in 6 present days on cell I10 (Earned) final result after +1 will be 7 Earned days .

Scenario #2
IF Present Days are 12 (6+6) minimum then Employee are allowed to get Two Paid Leave and this should add +1 in 12 present days on cell I10 (Earned) final result after +1 will be 13 Earned days .

Scenario #3
IF Present Days are 18 (6+6+6) minimum then Employee are allowed to get Thre Paid Leave and this should add +1 in 18 present days on cell I10 (Earned) final result after +1 will be 19 Earned days .

and sooon.....


any help/support on this will appreciated

Best,

Hi Special,
Thank you for your reply and help. Your formula in F10 works great, but can you make it possible in formula on Cell ' I10'.
As on F10 we want to see how may actual leave he has been, but paid leaves are only be give on following Scenarios
1 paid leave for 6 Present
2 paid leave for 12 present
3 paid leave for 18 present
4 paid leave for 24 present
5 paid leave for 30/31 present (depending on the month)

Thanks again

Best,

You mean like this

Please Login or Register  to view this content.

If yes then then not correct. It give Earned days 36 on 31 Present days which is not correct.

i.e: If present days 27 & leaves are 4 then employee get that 4 leaves paid and then total earn will be 31

O.K, so I think I got confused & lost as well and was not able to explain in a correct way, sorry for that.

I'll try again to explain what actually try to solved, hope this time i get succeed

It dosen't mateer how many Leaves employee has taken or has in his/her account. Maximum paid Leaves will be 4 or 5 depending on the Sundays in a month. Sometime it can be 4 and 5 Sundays in a month.

What we try to calculate and accomplish is when employee reaches on 6 present days and employee has any paid leaves in his account let say 3 for example and employee is present only for 5 days then employee with get only get 6 Earned days

Example:

1. On every 6 present - one Paid leave, only if employee has any leave in his account else 6 Earned days.
-Or- If employee has 6 present and has one Leave in his account then 6+1 =7 Earned days.

2. On every 12 present - two Paid leaves, same if employee has any leave in his account

3. And so on*......

Please see new sample Excel sheet attach

sample01.JPG



Best,

Any idea so far

I come up with this formula in G10

But not helping please see the illustration image

Any help on my strange problem will be highly appreciated, as i have tried many different way to figure out how this can be resolved but not getting an solution of it

For me as well unluckily

I have another Senior might we can Solve this 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:

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)

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

Thank you.

Best,