
Feb 13th, 2012, 07:47 AM
#1
Thread Starter
New Member
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

Feb 13th, 2012, 09:46 AM
#2
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.

Feb 15th, 2012, 01:03 PM
#3
Lively Member
Re: Calculate gratuity in excel VB
function calc_gratuity(y)
End function

Feb 15th, 2012, 01:04 PM
#4
Lively Member
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.

Feb 15th, 2012, 03:51 PM
#5
Frenzied Member
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)+((n5)*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 03:56 PM.

Feb 17th, 2012, 07:29 AM
#6
Thread Starter
New Member
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

Feb 17th, 2012, 07:48 AM
#7
Frenzied Member
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 07:50 AM.
Reason: keep missing letters when typing!

Feb 27th, 2012, 01:06 AM
#8
Thread Starter
New Member
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.

Feb 27th, 2012, 03:49 PM
#9
Frenzied Member
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)

Feb 28th, 2012, 08:48 AM
#10
Thread Starter
New Member
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.

Feb 28th, 2012, 10:26 AM
#11
Frenzied Member
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

Mar 3rd, 2012, 07:45 AM
#12
Thread Starter
New Member
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

Mar 3rd, 2012, 03:13 PM
#13
Frenzied Member
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 doable
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)

Mar 3rd, 2012, 09:26 PM
#14
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

Mar 12th, 2012, 01:58 AM
#15
Thread Starter
New Member
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

Mar 15th, 2012, 10:35 AM
#16
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

Forum Rules

Click Here to Expand Forum to Full Width
