Severance Calculator based on Provincial Rules

[kizzie37]

Hi Hope someone can assist. I want to create a formula to calculate someones Severance pay here are the rules:

Less than 3 month = 0
3 months to 1 year = 1 week
1 year to less than 3 years = 2 weeks
3 years = 3 weeks
Greater than 3 years = 3 weeks plus 1 weeks for every year worked thereafter

I have a formula that calculates the number of years worked between 2 dates (=(End Date-Start Date)/365. I need the number of weeks payable to display in Cell B18

 

[Dave Patton]

Did you read and/or try suggestion in post 9?

Why not use Lookup?
If you want to avoid the lookup, name the array of lookup information.

You didn't state what version of Excel you are using. You should update your profile.

 

[Dave Patton]

My post needs editing. If you want to avoid the lookup table, name the array of lookup information.

Your formula can probably be simpler.

Please provide complete definitions for the severance for each of the Provinces.

You could also post a few examples with expected results.

 

[Dave Patton]

In case someone reads this thread.
Complete information was not provided; use the relevant specifications for your industry and jurisdiction.
If you want to avoid the lookup table, you can name the array of lookup information (see E2)
The array of data for the Lookup is named rL; rL ={0,0;3,1;12,2;36,3;48,4;60,5;72,6;84,7;96,8}

The Lookup may be easier to maintain.

You could also post a few examples with expected results.

T202010b.xlsm
	A	B	C	D	E	F
1	Start	End	Months
2	01-Jan-13	31-Jul-21	103	8	8	8
3	01-Jun-21	31-Jul-21	2	0	0	0
4	31-Jul-19	31-Jul-21	24	2	2	2
5	31-Jul-18	31-Jul-21	36	3	3	3
6	31-Jul-18	31-Jul-21	36	3	3
5a

Cell Formulas
Range	Formula
C2:C6	C2	=DATEDIF(A2-1,B2,"m")
D2:D5	D2	=LOOKUP(C2,$I$2:$J$10)
E2:E5	E2	=LOOKUP(C2,rL)
F2:F5	F2	=(C2>=3)*1+(C2>=12)*1+(C2>=36)*MIN(6,(C2>=36)*(INT((C2-24)/12)))
D6	D6	=LOOKUP(DATEDIF(A6-1,B6,"m"),$I$2:$J$10)
E6	E6	=LOOKUP(DATEDIF(A6-1,B6,"m"),rL)

 

[kizzie37]

In case someone reads this thread.
Complete information was not provided; use the relevant specifications for your industry and jurisdiction.
If you want to avoid the lookup table, you can name the array of lookup information (see E2)
The array of data for the Lookup is named rL; rL ={0,0;3,1;12,2;36,3;48,4;60,5;72,6;84,7;96,8}

The Lookup may be easier to maintain.

You could also post a few examples with expected results.

T202010b.xlsm
	A	B	C	D	E	F
1	Start	End	Months
2	01-Jan-13	31-Jul-21	103	8	8	8
3	01-Jun-21	31-Jul-21	2	0	0	0
4	31-Jul-19	31-Jul-21	24	2	2	2
5	31-Jul-18	31-Jul-21	36	3	3	3
6	31-Jul-18	31-Jul-21	36	3	3
5a

Cell Formulas
Range	Formula
C2:C6	C2	=DATEDIF(A2-1,B2,"m")
D2:D5	D2	=LOOKUP(C2,$I$2:$J$10)
E2:E5	E2	=LOOKUP(C2,rL)
F2:F5	F2	=(C2>=3)*1+(C2>=12)*1+(C2>=36)*MIN(6,(C2>=36)*(INT((C2-24)/12)))
D6	D6	=LOOKUP(DATEDIF(A6-1,B6,"m"),$I$2:$J$10)
E6	E6	=LOOKUP(DATEDIF(A6-1,B6,"m"),rL)

Hi Dave, sorry i didnt realize I got a response, I dont want to use a lookup table my formula

In case someone reads this thread.
Complete information was not provided; use the relevant specifications for your industry and jurisdiction.
If you want to avoid the lookup table, you can name the array of lookup information (see E2)
The array of data for the Lookup is named rL; rL ={0,0;3,1;12,2;36,3;48,4;60,5;72,6;84,7;96,8}

The Lookup may be easier to maintain.

You could also post a few examples with expected results.

T202010b.xlsm
	A	B	C	D	E	F
1	Start	End	Months
2	01-Jan-13	31-Jul-21	103	8	8	8
3	01-Jun-21	31-Jul-21	2	0	0	0
4	31-Jul-19	31-Jul-21	24	2	2	2
5	31-Jul-18	31-Jul-21	36	3	3	3
6	31-Jul-18	31-Jul-21	36	3	3
5a

Cell Formulas
Range	Formula
C2:C6	C2	=DATEDIF(A2-1,B2,"m")
D2:D5	D2	=LOOKUP(C2,$I$2:$J$10)
E2:E5	E2	=LOOKUP(C2,rL)
F2:F5	F2	=(C2>=3)*1+(C2>=12)*1+(C2>=36)*MIN(6,(C2>=36)*(INT((C2-24)/12)))
D6	D6	=LOOKUP(DATEDIF(A6-1,B6,"m"),$I$2:$J$10)
E6	E6	=LOOKUP(DATEDIF(A6-1,B6,"m"),rL)

Hi Dave

thanks for the reply. I am using your formula in F2, there is still an issue. Here is what The rules are:

I modifed slightly to account for the 2 weeks, but now its not working and for folks who have completed 1 year its saying 0?

(B12>=3)*1+(B12>=12)*1+AND(B12>12,B12<36)*2+(B12>=36)*MIN(6,(B12>=36)*(INT((B12-24)/12)))

 

[Dave Patton]

Hello kizzie37

The information is ambiguous and you did not provide examples of the correct results.
Please
- edit your profile so we know what version of Excel that you are using
- include a complete explanation of how the severance is to be calculated
- show the date, day, month, or year when the person would first receive 1 week, 2 weeks, etc.
- the edit to the formula added a second 2 weeks to the calculation for certain terms
- the formula calculates 1 for 1 year 2 weeks are relevant for "more" than1 year
How is this determined 1 minute, 1 day, 1 week, 1 month ... ?

With the relevant information, a formula and/or Lookup can calculate the correct result.

You can post an extract of your examples and expected results with the forum's tool XL2BB.

 

[kizzie37]

HI Dave

its Excel 2018

Please see below I don't know how else to explain it. LOS = length or Service. In BC its as stated below:

the formula you gave works for longer lengths and 3 years but t snot calculating correctly from between 3 months to one year and between 1 year and 3 years

Severance.xlsx
	A	B	C
1
2
3
4
5	Province	BC
6
7
8
9
10	Start Date	June 18, 2012
11	Potential End Date	May 11, 2016
12	LOS	46
13	DOB
14	Age
15	Base Salary	$ 92,500
16
17	Employment Standards (ESA)
18	ESA PIL weeks	3
19	ESA PIL $	$ 5,336.54
Sheet1

Cell Formulas
Range	Formula
B12	B12	=DATEDIF(B10,B11,"m")
B18	B18	=IF(B5="BC",(B12>=3)*1+(B12>=12)*1+AND(B12>12,B12<36)*2+(B12>=36)*MIN(6,(B12>=36)*(INT((B12-24)/12))), IF(B5="AB",AND(B12>2,B12<=24)*1+AND(B12>24,B12<48)*2+AND(B12>=48,B12<72)*4+AND(B12>=72,B12<96)*5+AND(B12>=96,B12<120)*6+(B12>120)*8))
B19	B19	=B15/52*B18

Cells with Data Validation
Cell	Allow	Criteria
B5	List	BC, AB

 

[Dave Patton]

A suggestion pending information and calculation examples. I did not see your last post before I posted this.
Please review this and the next post.
The calculations assume the entire term is completed plus 1 minute. 3 months means 3 months plus 1 minute.

What is the calculation for exactly 3 months? What qualifies as more than 3 months?
What is the calculation for exactly 3 years? What qualifies as more than 3 years?
Use Formulas Evaluate Formula to review the formula

The lookup information E2 is the named Lookup information See Name Manager
name new aL
Value is ={0,0;3,1;12,2;36,3;48,4;60,5;72,6;84,7;96,8}
Review D2 =LOOKUP(C2,$I$2:$J$10) Is the Table correct?
Highlight the $I$2:$J$10 in D2's formula and press F9; the table information shows.
The formula still works with this data and this information is the value stated above for the named information.

T202010b.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Start	End	Months	Severance					Months	Severance	Notice period	Length of employment
2	1-Jan-21	28-Feb-21	2	0	0	0			0	0		3 Months or less
3	1-Jan-21	31-Mar-21	3	1	1	1			3	1	1 week	More than 3 months
4	1-Jan-21	31-Dec-21	12	2	2	2			12	2	2 weeks	More than 1 year
5	1-Jan-20	31-Dec-21	24	2	2	2			36	3	3 weeks	More than 3 years
6	1-Jan-19	31-Dec-21	36	3	3	3			48	4	4 weeks	More than 4 years
7	1-Jan-18	31-Dec-21	48	4	4	4			60	5	5 weeks	More than 5 years
8	1-Jan-17	31-Dec-21	60	5	5	5			72	6	6 weeks	More than 6 years
9	1-Jan-16	31-Dec-21	72	6	6	6			84	7	7 weeks	More than 7 years
10	1-Jan-15	31-Dec-21	84	7	7	7			96	8	8 weeks	More than 8 years
11	1-Jan-14	31-Dec-21	96	8	8	8
12	1-Jan-13	31-Dec-21	108	8	8	8
13	30-Jun-13	31-Dec-21	102	8	8	8
5a_2

Cell Formulas
Range	Formula
C2:C13	C2	=DATEDIF(A2,B2+1,"m")
D2:D13	D2	=LOOKUP(C2,$I$2:$J$10)
E2:E13	E2	=LOOKUP(C2,aL)
F2:F13	F2	=MIN(8,(C2>=3)+(C2>=12)+(C2>=36)*INT((C2-24)/12))

 

[kizzie37]

A suggestion pending information and calculation examples. I did not see your last post before I posted this.
Please review this and the next post.
The calculations assume the entire term is completed plus 1 minute. 3 months means 3 months plus 1 minute.

What is the calculation for exactly 3 months? What qualifies as more than 3 months?
What is the calculation for exactly 3 years? What qualifies as more than 3 years?
Use Formulas Evaluate Formula to review the formula

The lookup information E2 is the named Lookup information See Name Manager
name new aL
Value is ={0,0;3,1;12,2;36,3;48,4;60,5;72,6;84,7;96,8}
Review D2 =LOOKUP(C2,$I$2:$J$10) Is the Table correct?
Highlight the $I$2:$J$10 in D2's formula and press F9; the table information shows.
The formula still works with this data and this information is the value stated above for the named information.

T202010b.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Start	End	Months	Severance					Months	Severance	Notice period	Length of employment
2	1-Jan-21	28-Feb-21	2	0	0	0			0	0		3 Months or less
3	1-Jan-21	31-Mar-21	3	1	1	1			3	1	1 week	More than 3 months
4	1-Jan-21	31-Dec-21	12	2	2	2			12	2	2 weeks	More than 1 year
5	1-Jan-20	31-Dec-21	24	2	2	2			36	3	3 weeks	More than 3 years
6	1-Jan-19	31-Dec-21	36	3	3	3			48	4	4 weeks	More than 4 years
7	1-Jan-18	31-Dec-21	48	4	4	4			60	5	5 weeks	More than 5 years
8	1-Jan-17	31-Dec-21	60	5	5	5			72	6	6 weeks	More than 6 years
9	1-Jan-16	31-Dec-21	72	6	6	6			84	7	7 weeks	More than 7 years
10	1-Jan-15	31-Dec-21	84	7	7	7			96	8	8 weeks	More than 8 years
11	1-Jan-14	31-Dec-21	96	8	8	8
12	1-Jan-13	31-Dec-21	108	8	8	8
13	30-Jun-13	31-Dec-21	102	8	8	8
5a_2

Cell Formulas
Range	Formula
C2:C13	C2	=DATEDIF(A2,B2+1,"m")
D2:D13	D2	=LOOKUP(C2,$I$2:$J$10)
E2:E13	E2	=LOOKUP(C2,aL)
F2:F13	F2	=MIN(8,(C2>=3)+(C2>=12)+(C2>=36)*INT((C2-24)/12))

Hi Dave

Thank you. I prefer not to use a lookup table and use a straight formula, it would seem almost there. We would determine 1 day past one year or 3 months or 3 years

I hope that helps

 

[Dave Patton]

T202010b.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Start	End	Months	Severance					Months	Severance	Notice period	Length of employment
2	1-Jan-21	28-Feb-21	2		0			TRUE	0	0		3 Months or less
3	1-Jan-21	3-Apr-21	3		1			TRUE	3	1	1 week	More than 3 months
4	1-Jan-21	3-Jan-22	12		2			TRUE	12	2	2 weeks	More than 1 year
5	1-Jan-20	3-Jan-22	24		2			FALSE	36	3	3 weeks	More than 3 years
6	1-Jan-19	3-Jan-22	36		3			TRUE	48	4	4 weeks	More than 4 years
7	1-Jan-18	3-Jan-22	48		4			TRUE	60	5	5 weeks	More than 5 years
8	1-Jan-17	3-Jan-22	60		5			TRUE	72	6	6 weeks	More than 6 years
9	1-Jan-16	3-Jan-22	72		6			TRUE	84	7	7 weeks	More than 7 years
10	1-Jan-15	3-Jan-22	84		7			TRUE	96	8	8 weeks	More than 8 years
11	1-Jan-14	3-Jan-22	96		8			TRUE
12	1-Jan-13	3-Jan-22	108		8			TRUE
13	30-Jun-13	3-Jan-22	102		8			TRUE
5a_3

Cell Formulas
Range	Formula
C2:C13	C2	=DATEDIF(A2,B2+1,"m")
E2:E13	E2	=LOOKUP(C2,aL)
H2	H2	=YEARFRAC(A2,B2,1)<3/12
H3	H3	=YEARFRAC(A3,B3,1)>3/12
H4	H4	=YEARFRAC(A4,B4,1)>1
H5:H6	H5	=YEARFRAC(A5,B5,1)>3
H7	H7	=YEARFRAC(A7,B7,1)>4
H8	H8	=YEARFRAC(A8,B8,1)>5
H9	H9	=YEARFRAC(A9,B9,1)>6
H10	H10	=YEARFRAC(A10,B10,1)>7
H11:H13	H11	=YEARFRAC(A11,B11,1)>8

 

[Dave Patton]

The changes to the formula were not required.
The suggestion is a little more concise; try reviewing the formula with Excel's Formula Evaluate.

T202010b.xlsm
	A	B	C	D	E	F	G
1	Start	End	Months	Severance
2	1-Jan-21	28-Feb-21	2		0	0
3	1-Jan-21	3-Apr-21	3		1	1
4	1-Jan-21	3-Jan-22	12		2	2
5	1-Jan-20	3-Jan-22	24		2	2
6	1-Jan-19	3-Jan-22	36		3	3
7	1-Jan-18	3-Jan-22	48		4	4
8	1-Jan-17	3-Jan-22	60		5	5
9	1-Jan-16	3-Jan-22	72		6	6
10	1-Jan-15	3-Jan-22	84		7	7
11	1-Jan-14	3-Jan-22	96		8	8
12	1-Jan-13	3-Jan-22	108		8	8
13	30-Jun-13	3-Jan-22	102		8	8
5a_3

Cell Formulas
Range	Formula
E2:E13	E2	=LOOKUP(C2,aL)
F2:F13	F2	=MIN(8,(C2>=3)+(C2>=12)+(C2>=36)*INT((C2-24)/12))
C2:C13	C2	=DATEDIF(A2,B2+1,"m")