Results 1 to 16 of 16

Thread: Calculate gratuity in excel VB

Hybrid View

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2001
    Location
    dubai
    Posts
    6

    Calculate gratuity in excel VB

    I want to write small code in excel vb editor to calculate gratuity amount.

    Rules is if the employee has completed between 1 - 2 years of service he is entitled to 7 days of Basic salary

    If the employee has completed between 2 - 4 years of service he is entitled
    to 14 days of Basic salary

    If the employeed has completed 5 years of service he is entitled to 21 days
    of Basic salary

    If completed more than 5 years he is entitled to 30 days of Basic salary,But
    here the catch is for first 5 years he will get 21 days and balance years will
    be calculated in 30 days basic pay

  2. #2
    Stack Overflow mod​erator
    Join Date
    May 2008
    Location
    British Columbia, Canada
    Posts
    2,824

    Re: Calculate gratuity in excel VB

    Welcome to VBForums!

    This belongs on one of the question forums, not in the CodeBank, which is for code samples or working projects. A moderator will get it moved eventually, but you should post future questions in the Office Development forum so that more people with the relevant knowledge will be able to see your question.

  3. #3
    Lively Member
    Join Date
    Jan 2009
    Posts
    93

    Re: Calculate gratuity in excel VB

    function calc_gratuity(y)

    End function

  4. #4
    Lively Member
    Join Date
    Jan 2009
    Posts
    93

    Re: Calculate gratuity in excel VB

    Oops hit enter too soon. I also meant to say, you pass in the years of service, then add some IF or Select Case code to calculate the gratuity based on the y value passed into the function.

  5. #5
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,470

    Re: Calculate gratuity in excel VB

    you really need to be absolute when you enter rules based things like this...

    what happens if he has only worked 11 months?

    i will asume he gets nothing!

    and are these a lotted days pay multiplied by the years of service?

    i wil asume yes!

    the simplest solution is to draw out the alottment as a function of years of service

    Code:
    Rules is if the employee has completed between 1 - 2 years of service he is entitled to 7 days of Basic salary
    
    y(1)=7
    y(2)=7
    
    If the employee has completed between 2 - 4 years of service he is entitled
    to 14 days of Basic salary
    
    y(3)=14
    y(4)=14
    
    If the employeed has completed 5 years of service he is entitled to 21 days
    of Basic salary
    
    y(5)=21
    
    If completed more than 5 years he is entitled to 30 days of Basic salary,But 
    here the catch is for first 5 years he will get 21 days and balance years will
    be calculated in 30 days basic pay 
    
    y(6)=30
    y(7)=30
    ..
    ..
    y(n)=30

    if you set up such an array you can simpy sum the values of the arrays

    you could precalculate the vales and fill the arrays so that a given years service is y(given years)=98 days or whatever!

    its a do all the arithmatic up front approach!

    Code:
    y(1)=7
    y(2)=14
    y(3)=28
    y(4)=42
    y(5)=63
    y(6)=93
    y(7)=123
    ..
    ..
    y(n)=y(5)+((n-5)*30)
    here to help ( i can always write the formula out if you really want y(n)=...from the very start y(0))
    Last edited by incidentals; Feb 15th, 2012 at 04:56 PM.

  6. #6

    Thread Starter
    New Member
    Join Date
    Aug 2001
    Location
    dubai
    Posts
    6

    Re: Calculate gratuity in excel VB

    Thanks Incidentals.

    I appreciate your help.But there is one catch and that is we have to convert service period to days.

    It is like this
    Date of Joining 09/07/1995
    Date of Resign. 30/09/2011
    Total days worked 5895

    Can you please write the whole code so that I can use it in Macro

  7. #7
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,470

    Re: Calculate gratuity in excel VB

    you can use the date functions built into the office apps...

    your application has been badly described - hence my interpretation - appears to be in error (NOT)

    you need to be very very very detailed in your description of the problem, if you want to be able to build and understand the solution...

    please respecify your outline problem in terms that will illicit the appropriate coded solution...

    here to help
    Last edited by incidentals; Feb 17th, 2012 at 08:50 AM. Reason: keep missing letters when typing!

  8. #8

    Thread Starter
    New Member
    Join Date
    Aug 2001
    Location
    dubai
    Posts
    6

    Re: Calculate gratuity in excel VB

    Hi Incidentals and Todd_kauffman

    I have finally wrote the code and it seems it is working but I want you to look at it and do amendments necessary.

    Here is the code for your reference :-

    Range("A3").select excel sheet Cell A3(where total no.of days worked to be put manually by the user


    sub gratuitycalc()

    Range("B4").select excel sheet Cell B4(where the result of no.of days gratuity entitled for the staff)

    If range("A3").value<364 then
    range("B4").value = 0
    end if

    If range("A3").value>=365 then
    range("B4").value=range("B4") * 7/365

    If range("A3").value>730 then
    range("B4").value=range("B4") * 14/365

    If range("A3").value>=1825 then
    range("B4").value=range("B4") * 21/365

    If range("A3").value>1825 then
    range("B4").value=(range("B4")-1825) * 30/365

    endif
    endif
    endif
    endif

    end sub

    Moreover I also have one more problem that is either the employee has been
    terminated or he himself has resigned.

    I want to put manually say in Cell C3 the letter T(terminated) or R (Resigned)

    I want my code to first check whether Cell C3 has T or R and then do the above calculation.

  9. #9
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,470

    Re: Calculate gratuity in excel VB

    you need to stop your dependancy on ifs

    the problem with them is that you either have to force junps to places in your subroutines or escapes from the sub entirely!

    use the select case, its easy to read and ammend and you will not get fall through ( thats where the condition you first do somethiong to is not the only condition that your data matched!)

    here to help ( example of select if needed)

  10. #10

    Thread Starter
    New Member
    Join Date
    Aug 2001
    Location
    dubai
    Posts
    6

    Re: Calculate gratuity in excel VB

    Thanks Incidentals,

    As per your suggestion I have written the new code as follows

    Sub gratuityfunction()
    Select Case Range("A4").value
    Case Is < 365
    Range("B4").value = 0
    Case 365 To 730
    Range("B4").value = Range("a4").value * 7 / 365
    Case 731 To 1095
    Range("B4").value = Range("A4").value * 14 / 365
    Case 1096 To 1825
    Range("B4").value = Range("A4").value * 21 / 365
    Case Is > 1825
    Range("B4").value = 105 + (Range("A4").value - 1825) * 30 / 365
    End Select
    End Sub

    But still I have 1 problem & that is if the employee has resigned or terminated.
    Like I said earlier that I will manually put the letter T or R in cell C2.I want the programe to check first cell C2 and then execute the programe.Because in case of termination the employee will get for the first 3 years of service 14 days of gratuity and then from 3 to 5 years of service he will get 21 days of gratuity and finally after 5 years of service 30 days.

  11. #11
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,470

    Re: Calculate gratuity in excel VB

    there is a little missing in your description of the problem

    I understand that you have a concern around termination and resignation.

    but just exactly what effect you wish impart due to the qbove is not at all clear.

    do you

    (1) wish to not calculate anything if the employee resigns
    (2) wish to calculate the gratuity upto the date of resignation
    (3) wish to calculate the gratuity if the employee was terminated, but only upto the date of termination
    (4) wish to calculate the gratuity if the employee is still employed , upto now

    here to help

  12. #12

    Thread Starter
    New Member
    Join Date
    Aug 2001
    Location
    dubai
    Posts
    6

    Re: Calculate gratuity in excel VB

    Hi Incidentals.

    As per your query:-
    1) Gratuity has to be calculated even though the employee has resigned.The only condition is the employee has to complete 1 year of service.

    2) Gratuity has to be calculated upto last working days whether the employee
    has resigned or terminated.

    The problem I am facing is how to write the code,so that the programe should
    first check whether its R(resigned) or T(terminated).

    The rules in termination is:-
    A) If the employee has completed 1 - 2 years of service,he will get 7 days of
    salary.

    B) If the employee has completed 2 - 5 years of service,he is entitled to 21
    days of salary.

    C) If the employee has completed more than 5 years of service,he is entited to 30 days of salary

    Rules for resignation:-

    You can refer to my Select Case code that I have posted.

    Bye

  13. #13
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,470

    Re: Calculate gratuity in excel VB

    what you have really said is that you calculate upto today unless they are T or R if they have worked over 1 year.

    I teach people to keep turning the logic about untill they see something that makes easy code.

    I think you could have said...

    if they have worked more than a year then calculate upto their final day if they have T or R or now which ever is earlier

    I think though that you will have the last day of employment available so...

    If they have worked more than 1 year calculate upto their final day or now which ever is earliest.

    That looks a lot more consise and do-able

    what do you think?

    If they have a last day is must be before or equal to now so just use their lastday otherwise use now

    if you use todate as avariable then start with todate=now() and use if lastdate>"" then todate= lastdate

    or something like that

    then simply put todate into you calculation where you currently put now()

    here to help (more if needed)

  14. #14
    PowerPoster Spoo's Avatar
    Join Date
    Nov 2008
    Location
    Right Coast
    Posts
    2,656

    Re: Calculate gratuity in excel VB

    Anwar

    A general suggestion .. use Code wrapper.

    When you did your post #10 it was nicely indented.
    But you notice that it appears without any indents.

    Here is your same post using the Code wrapper.
    Code:
    Sub gratuityfunction()
    Select Case Range("A4").value
         Case Is < 365
            Range("B4").value = 0
        Case 365 To 730
            Range("B4").value = Range("a4").value * 7 / 365
        Case 731 To 1095
            Range("B4").value = Range("A4").value * 14 / 365
        Case 1096 To 1825
            Range("B4").value = Range("A4").value * 21 / 365
        Case Is > 1825
            Range("B4").value = 105 + (Range("A4").value - 1825) * 30 / 365
    End Select
    End Sub
    Looks a little more like you intended, eh ..

    Spoo

  15. #15

    Thread Starter
    New Member
    Join Date
    Aug 2001
    Location
    dubai
    Posts
    6

    Re: Calculate gratuity in excel VB

    Hi Incidentals,

    Still my problem is how do I make the code to check first If the employee has
    T(Terminated) or R(Resigned) and then calculate gratuity.Because as I have
    mentioned earlier the the rule is different for Termination and Resignation.

    The rules in termination is:-
    A) If the employee has completed 1 - 2 years or(730 days) of service,he will get 7 days of salary


    B) If the employee has completed 2 - 5 years or(731 to 1825 days) of service,he is entitled to 21 days of salary.

    C) If the employee has completed more than 5 years of service,he is entited to 30 days of salary

  16. #16
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Calculate gratuity in excel VB

    If you're looking for a specific value in a cell, you can do it like this:

    Code:
    If Cells(Row, Column) = "T" Then
            'do something
        ElseIf Cells(Row, Column) = "R" Then
            'do something else
        Else
            'do something else when NOT T or R
        End If
    You would change "Row" and "Column" to be numbers, ie. cells(1,1) is the same as A1, etc.

Posting Permissions

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



Click Here to Expand Forum to Full Width