Vacation Pay Calculation

[Guest]

Can anyone give a formula in a cell without many arguments in other columns?

I have a spreadsheet for staff provisions like vacation pay, severance pay
and air ticket which I have to update on monthly basis.

For vacation pay, the calculation is like this:

First 5 years. 14 days pay per year and afterwards 21 days per year. So the
calculation goes like this: (Here (in Kuwait) one month is considered as 26
days)

Basic pay/26*14/365*Number of days for the first five years or 1825 days

Later

Basic pay/26*21/365*Number of days after the first five years or 1825 days.

But if a person returns from vacation on completion of 4 years, and goes for
vacation on completion of 6 years, he will get 14 days pay for 5th year and
21 days pay for 6th year. I hope it is clear.

Now I am doing this by putting many arguments like <5 years, >5 years in
helper columns and hiding

I have Bade No., Name, Basic Pay, First Entry, Last Entry in columns: A to
E. I need the formula in Column F.

Can anyone help?

Thanks in advance.

Jaleel

 

[sloth]

Not sure whether or not I understood the question. Maybe this will
help:

=if((vacation.date-hire.date)<(365*tier2.year),tier1.vac,tier2.vac)

For example,
hire.date = 5/10/2000
tier2.year = 5
tier1.vac = 14
tier2.vac = 21

vacation.date = 5/9/2005 then returns: 14
vacation.date = 5/10/2005 then returns: 21
vacation.date = 5/11/2005 then returns: 21

 

[Guest]

Sloth,

Thanks for your efforts. Can you please explain what is meant by
tier2.year, tier1.vac and tier2.vac?

Regards,

Jaleel

 

[sloth]

Tier2.year is a name for the year that vacation days turns from 14 to
21, or year 5.

Tier1.vac is a name for # of vacation days if time with co. is less
than 5 years
Tier2.vac is a name for # of vacation days if time with co. is greater
than or equal to 5 years

Disregard the names if it helps and just use the values, i.e. 14, 21,
and 5.

 

[Guest]

Thank you for your explanation. It will bring the result of one’s
eligibility is either 14 or 21. That is not the main issue. Last entry is
also to be considered. Suppose, one fellow joined the company on 01/01/2000.
His last arrival from vacation is 01/01/2004. He is going for next vacation
on 01/01/2006. He is not fully eligible for 21 days pay. His first 5 years
will be completed on 01/01/2005. So for that period he will get only 14 days
pay. And for the rest of the period up to 01/01/2006 he is eligible for 21
days pay. This calculation is making the complication.

Jaleel

 

[sloth]

I'm still a little confused over what exactly you're trying to
calculate. How about something like this that calculates total
eligible vacation days given a starting date (hire date) and ending
date (current date, vacation date, etc.)? Note that this calc assume
vacation days are accrued every x days, where x = 365/# of eligible
vacation days per year, i.e. 365/21 = ~26; and there is also some
imprecision based on years with fewer than 365 days, i.e. leap years.

hire.date = 1/1/2000

=int(if((end.date-hire.date)/365<5, (end.date-hire.date)/365*14,
5*14+(((end.date-hire.date)/365)-5)*21))

end.date = 1/1/2004 => 56
end.date = 1/1/2005 => 70
end.date = 1/1/2006 => 91

end.date = 7/1/2006 => 101

If an employee is eligible for all their annual vacation days on their
hiring anniversary, you can change the equation as follows:

=if((end.date-hire.date)/365<5, int((end.date-hire.date)/365)*14,
5*14+int((((end.date-hire.date)/365)-5))*21)

Does this help?