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]

Please check this formula; you can use Excel's Formula Evaluate.
Ensure it matches the legal requirements.

T202010b.xlsm
	A	B
16	Start	1-Jan-00
17	End	20-May-20
18	Months	244
19	Severance	20
20
5a

Cell Formulas
Range	Formula
B18	B18	=DATEDIF(Start,End,"m")
B19	B19	=AND(B18>2,B18<=12)*1+(B18<36)*2+(B18=36)*1+(B18>36)*(3+INT((B18-36)/12))

Named Ranges
Name	Refers To	Cells
End	='5a'!$B$17	B18
Start	='5a'!$B$16	B18

 

[kizzie37]

Please check this formula; you can use Excel's Formula Evaluate.
Ensure it matches the legal requirements.

T202010b.xlsm
	A	B
16	Start	1-Jan-00
17	End	20-May-20
18	Months	244
19	Severance	20
20
5a

Cell Formulas
Range	Formula
B18	B18	=DATEDIF(Start,End,"m")
B19	B19	=AND(B18>2,B18<=12)*1+(B18<36)*2+(B18=36)*1+(B18>36)*(3+INT((B18-36)/12))

Named Ranges
Name	Refers To	Cells
End	='5a'!$B$17	B18
Start	='5a'!$B$16	B18

Hi Dave, thanks for this the formula in B19 is not correctly working for less than 3 months or 3 months to one year.

For 6 months its showing 3 weeks or pay and for less than 3 months its showing 2 weeks of pay?

 

[Dave Patton]

T202010b.xlsm
	A	B
16	Start	1-Sep-20
17	End	31-Dec-20
18	Months	3
19	Severance	1
20
5a

Cell Formulas
Range	Formula
B18	B18	=DATEDIF(Start,End,"m")
B19	B19	=AND(B18>2,B18<=12)*1+AND(B18>12,B18<36)*2+(B18=36)*3+(B18>36)*(3+INT((B18-36)/12))

Named Ranges
Name	Refers To	Cells
End	='5a'!$B$17	B18
Start	='5a'!$B$16	B18

 

[kizzie37]

Dave, thank you looks good now!

 

[Dave Patton]

You could try using a Lookup table but you would still need to add the last part

T202010b.xlsm
	A	B	C	D	E	F	G
15						0	0
16	Start	1-Jan-18		2		3	1
17	End	31-Dec-20		2		35	2
18	Months	35				36	3
19	Severance	2
5a

Cell Formulas
Range	Formula
D16	D16	=LOOKUP(B18,F15:G18)+(B18>36)*INT((B18-36)/12)
D17	D17	=LOOKUP(B18,{0,0;3,1;35,2;36,3})+(B18>36)*INT((B18-36)/12)
B18	B18	=DATEDIF(Start,End,"m")
B19	B19	=AND(B18>2,B18<=12)*1+AND(B18>12,B18<36)*2+(B18=36)*3+(B18>36)*(3+INT((B18-36)/12))

 

[kizzie37]

T202010b.xlsm
	A	B
16	Start	1-Sep-20
17	End	31-Dec-20
18	Months	3
19	Severance	1
20
5a

Cell Formulas
Range	Formula
B18	B18	=DATEDIF(Start,End,"m")
B19	B19	=AND(B18>2,B18<=12)*1+AND(B18>12,B18<36)*2+(B18=36)*3+(B18>36)*(3+INT((B18-36)/12))

Named Ranges
Name	Refers To	Cells
End	='5a'!$B$17	B18
Start	='5a'!$B$16	B18

Hi Dave. Your solution was working great until I got to someone who had a long tenure. I forgot to say in my question

Greater than 3 years = 3 weeks plus 1 weeks for every year worked thereafter

that it was up to a max of 8 weeks.

Can you tweak the formula so that this works correctly?

 

[Dave Patton]

Your profile doesn't show your Excel version. I added am example with Excel 365' Let function.

I may not have retained the file with the example that you show. Please test the version that you prefer.

T202010b.xlsm
	A	B	C	D	E
14			with 365
15	Start	End	Severance	Severance	Severance
16	31-Dec-10	31-Dec-20	8	8	8
5a

Cell Formulas
Range	Formula
C16	C16	=LET(mo,DATEDIF(Start,End,"m"),LOOKUP(mo,aL)+(mo>36)*MIN(5,INT((mo-36)/12)))
D16	D16	=MIN(8,LOOKUP(DATEDIF(Start,End,"m"),aL)+(DATEDIF(Start,End,"m")>36)*INT((DATEDIF(Start,End,"m")-36)/12))
E16	E16	=LOOKUP(DATEDIF(Start,End,"m"),aL)+(DATEDIF(Start,End,"m")>36)*MIN(5,INT((DATEDIF(Start,End,"m")-36)/12))

Named Ranges
Name	Refers To	Cells
'5a'!End	='5a'!$B$16	C16:E16
'5a'!Start	='5a'!$A$16	C16:E16

 

[Dave Patton]

T202010b.xlsm
	A	B	C	D	E	F	G	H
1	Start	End					Months	Severance
2	31-Dec-00	31-Dec-20	8	8	8		0	0
3							3	1
4							12	2
5							36	3
6							48	4
7							60	5
8							72	6
9							84	7
10							96	8
11
5a

Cell Formulas
Range	Formula
C2	C2	=LOOKUP(DATEDIF(A2,B2,"m"),aL)+(DATEDIF(A2,B2,"m")>36)*MIN(5,INT((DATEDIF(A2,B2,"m")-36)/12))
D2	D2	=LOOKUP(DATEDIF(A2,B2,"m"),G2:H10)
E2	E2	=LOOKUP(DATEDIF(A2,B2,"m"),rL)

Named Ranges
Name	Refers To	Cells
rL	='5a'!$G$2:$H$10	D2:E2

 

[kizzie37]

Hi

Your profile doesn't show your Excel version. I added am example with Excel 365' Let function.

I may not have retained the file with the example that you show. Please test the version that you prefer.

T202010b.xlsm
	A	B	C	D	E
14			with 365
15	Start	End	Severance	Severance	Severance
16	31-Dec-10	31-Dec-20	8	8	8
5a

Cell Formulas
Range	Formula
C16	C16	=LET(mo,DATEDIF(Start,End,"m"),LOOKUP(mo,aL)+(mo>36)*MIN(5,INT((mo-36)/12)))
D16	D16	=MIN(8,LOOKUP(DATEDIF(Start,End,"m"),aL)+(DATEDIF(Start,End,"m")>36)*INT((DATEDIF(Start,End,"m")-36)/12))
E16	E16	=LOOKUP(DATEDIF(Start,End,"m"),aL)+(DATEDIF(Start,End,"m")>36)*MIN(5,INT((DATEDIF(Start,End,"m")-36)/12))

Named Ranges
Name	Refers To	Cells
'5a'!End	='5a'!$B$16	C16:E16
'5a'!Start	='5a'!$A$16	C16:E16

Dave, thanks for replying, Sorry If I'm being dumb, I am a little confused now. This is the ask:

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 week for every year worked thereafter (up to a mx of 8 weeks)

So far the formula I am using is:

=IF(B5="BC",AND(B12>2,B12<=12)*1+AND(B12>12,B12<36)*2+(B12=36)*3+(B12>36)*(3+INT((B12-36)/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))

B5 = Region
B2 is "length of Service" in months

I'm a bit confused how the new bit fits in with all that. I would prefer not to use a lookup if there is a way round it?