Condition Formula

I am trying to compute severance pay for employees whose contracts are to be terminated soon.. The pay out is based on length of service

I need a formula which can compute all in one cell....the length of service and entitlement

STAFF NAME APPOINTMENT DATE Termination Date Monthly Salary Service Time(Years) Answer
Stephen 09/08/2012 30/06/2024 10,000 12 30,000
Agnes 01/05/2017 30/06/2024 8,000 7 16,000
Solomon 01/04/2021 30/06/2024 7,000 3 7,000
Innocent 18/12/2023 30/06/2024 2,000 1 1,000



Condition Entitlement
more than 6 months but less than 1 year 2 Weeks pay
more than 12 months but less than 5 years 1 Months pay
more than 5 years but less than 10 years 2 Months pay
10 years or more 3 Months Pay

Could use the formula below.

Formula:

Please Login or Register  to view this content.

and the table:
Excel 365 (Windows) 64 bit

	E	F
15	1	0,5
16	5	1
17	10	2
18	40	3

Sheet: Sheet1