# Need help on Present/Leave and Earned days

1. ## 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,

2. ## Re: Need help on Present/Leave and Earned days

So
1 day for 6 Present
2 days for 12 present
3 days for 18 present
"and so on"

So 1 day for every 6

So its just

in F10
=QUOTIENT(D10,6)
which would be three days.

3. ## Re: Need help on Present/Leave and Earned days

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,

4. ## Re: Need help on Present/Leave and Earned days

So just put the formula in I10.
But if you want 21 then you'll need to add D10 to the formula.

5. ## Re: Need help on Present/Leave and Earned days

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

6. ## Re: Need help on Present/Leave and Earned days

Why are you suddenly subtracting H10? That's not what's in your sample file in I10 (though this shouldnt affect anything if they are zero). But you shouldnt be changing the formula of the sample file you provided.

D10 is 18, F10 is 3, I10 is D10+F10-(E10*0.5) which is 21
You dont need brackets for E10*0.5

D10 is 18
I10 should be D10+QUOTIENT(18,6)-E10*0.5
QUOTIENT(18,6) is 3 which is F10
E10 is 0
I10 gives D10+F10-E10*0.5 which is 21. Correct.

D10 is 27
I10 should be D10+QUOTIENT(27,6)-E10*0.5
QUOTIENT(27,6) is 4
E10 is 0
I10 gives D10+F10-E10*0.5 which is 31. Correct.

7. ## Re: Need help on Present/Leave and Earned days

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,

8. ## Re: Need help on Present/Leave and Earned days

Any idea so far

I come up with this formula in G10

But not helping please see the illustration image

9. ## Re: Need help on Present/Leave and Earned days

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

10. ## Re: Need help on Present/Leave and Earned days

Too complicated for me.

11. ## Re: Need help on Present/Leave and Earned days

For me as well unluckily

12. ## Re: Need help on Present/Leave and Earned days

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,

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1