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

Welcome to VBForums! :D

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.

function calc_gratuity(y)

End function

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.

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))

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

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

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.

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)

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.

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

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

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)

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

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

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.