How To Calculate Long Service Leave

Lukens Orms1986 · 06-07-2017,09:xvi AM

06-07-2017,09:xvi AM #1

Ribband is offline

Registered User

Long service award calculator

Hi
Demand aid with a calculator to determine the type of long service laurels a person would be entitled to.

Assume a1=start appointment of employee, b2=today'due south date, a2=xv,a3=25,a4=35 and a5=50

Results needed
For less than 15 years service (a2) upshot blank
between 15 and 25 (a3) upshot xv Y.S.A (YSA=years service award)
between 25 and 35 (a4) effect 25 Y.Southward.A
etc

I take put the years service in a2 to a5 so that if the service award year changes, all I would accept to input is the new service award year lengths

Hoping you guys tin help

06-07-2017,09:21 AM #2

DannyJ is offline

Forum Contributor

Re: Long service award calculator

I recollect you have your cells all wrong. Yous can't put the amount of years service in A2 - A5 and then likewise have the formula in there.

06-07-2017,09:23 AM #three

DannyJ is offline

Forum Contributor

Re: Long service honour calculator

=IF(AND(YEAR($B$2)-YEAR($A$1)>A2-1,Twelvemonth($B$ii)-YEAR($A$1)<A3),"YSA","NO")
That should do what yous desire.

For the final of them you need to take the and out and only have it as Greater than the terminal number.

=IF(YEAR($B$2)-YEAR($A$1)>A4-1,"YSA","NO")

Last edited by DannyJ; 06-07-2017 at 09:30 AM.

06-07-2017,09:38 AM #4

Ribband is offline

Registered User

Re: Long service award calculator

No you lot're right, I take made a slight fault on the results front
It should read in a new jail cell, (c1)
for less than xv result blank
between fifteen and 25 outcome 15 Y.S.A
between 25 and 35 issue 25 Y.South.A
betwixt 35 and 50 result 35 Y.Southward.A
l or more result 50 Y.S.A

06-07-2017,09:41 AM #5

DannyJ is offline

Forum Correspondent

Re: Long service award figurer

Put that formula in C1 and drag downward. When you go to 50 or more and then employ the 2d formula.

06-07-2017,09:51 AM #half dozen

Re: Long service award calculator

Hi, Ribband.
Check out the attached spreadsheet.

06-07-2017,ten:05 AM #vii

Ribband is offline

Registered User

Re: Long service honour estimator

Looking adept and so far merely the results I go is either YSA or No. Is it possible to include the type of laurels as well ie 15 YSA, 25 YSA, 35 YSA?

06-07-2017,ten:06 AM #8

DannyJ is offline

Forum Contributor

Re: Long service honour figurer

In the if statements just change where it says "YSA" to "xv YSA" or whichever one y'all want it to be.

06-07-2017,10:08 AM #9

Ribband is offline

Registered User

Re: Long service laurels calculator

Lol
Spot on thank you both very much.

Can believe how fast you guys are.

06-07-2017,10:xi AM #10

DannyJ is offline

Forum Correspondent

Re: Long service honor computer

No worries.