+ Reply to Thread
Results 1 to 12 of 12

Need help on Present/Leave and Earned days

  1. #1
    Forum Contributor
    Join Date
    06-09-2014
    Location
    Sweden
    Posts
    305

    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,
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    7,003

    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.

    Any other answer would invalidate your "and so on" rule.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Contributor
    Join Date
    06-09-2014
    Location
    Sweden
    Posts
    305

    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. #4
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    7,003

    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. #5
    Forum Contributor
    Join Date
    06-09-2014
    Location
    Sweden
    Posts
    305

    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. #6
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    7,003

    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.

    Your sample file says
    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.

    Your other Example
    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. #7
    Forum Contributor
    Join Date
    06-09-2014
    Location
    Sweden
    Posts
    305

    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,
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    06-09-2014
    Location
    Sweden
    Posts
    305

    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
    Attached Images Attached Images

  9. #9
    Forum Contributor
    Join Date
    06-09-2014
    Location
    Sweden
    Posts
    305

    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. #10
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    7,003

    Re: Need help on Present/Leave and Earned days

    Too complicated for me.

  11. #11
    Forum Contributor
    Join Date
    06-09-2014
    Location
    Sweden
    Posts
    305

    Re: Need help on Present/Leave and Earned days

    For me as well unluckily

  12. #12
    Forum Contributor
    Join Date
    06-09-2014
    Location
    Sweden
    Posts
    305

    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,

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Leave tracker - how to use formula to automate the total leave days
    By yiyi2 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-16-2018, 10:59 PM
  2. Formula for vacation days earned annualy with additional days at a milestone
    By poTATEohhh in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-29-2015, 08:40 AM
  3. [SOLVED] Stuck into Present & Total Earned Days :(
    By pipsmultan in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 06-26-2015, 10:46 AM
  4. Replies: 1
    Last Post: 07-31-2014, 01:43 AM
  5. Need only leave days one by one from leave attendace full data
    By sathiyamoorthy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-18-2014, 03:23 AM
  6. Removing text from a string if present, otherwise leave as-is
    By wace in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-19-2010, 09:00 AM
  7. [SOLVED] calculating earned leave
    By John Smith in forum Excel General
    Replies: 8
    Last Post: 05-30-2006, 01:55 PM

Bookmarks

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