• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculating Severance Pay


New Member

I need help with putting together formulas in to calculate severance like this.

Common Law = amount of weeks pay depending on length of service
Length of Service
Weeks of Severance Pay
<1 year
4 weeks
<2 years
8 weeks
<3 years
12 weeks
<4 years
16 weeks
<5 years
20 weeks
<6 years
24 weeks
More than 6 years
28 weeks

and then
  1. An employee has 16 weeks of severance pay due to them.
  2. The employee's current monthly salary is $3,479.00
  3. How much severance pay is due to the employee?

    16 weeks of severance pay * 40 hours = 640 total hours
    $3,479.00 / 174 = $19.99 an hour
    640 total hours * $19.99 (hourly rate) = $12,793.60 of severance pay due to the employee
My fields would be :
first name, last name, DOB, Age (formula that calculates age), start date, end date, length of service (formula that calculates), salary, weekly salary, daily salary,weeks of pay they are entitled for according to law, and then there severance pay

Peter Bartholomew

Well-Known Member
If 'Service' were the number of years service rounded up, a key element of the formula would be the entitlement in weeks
= IF(Service<=6, 4*Service, 28)

Peter Bartholomew

Well-Known Member
This workbook was very unusual for me, I normally work on tables of data rather than forms with individual values. As a result, I tend to work with Named Ranges that are complete columns (I suspect my use of a name was something that confused you above?). Because naming is a key step in my understanding of any workbook I have here introduced sheet-local names of the form
applied to single cells, so that it would be possible to copy the worksheet for other employees without messing up the names. I also came to the conclusion that to store service in the form 'years service rounded up' was not a natural way of working so I used the term (1 + serviceLength) [years completed] in a number of alternative formulas

If I have got it right, they all should give the same value so I suggest you pick the version you find to be the clearest.