Calculating Severance Pay - labour End of Service

[aliibrahem]

Hello Everyone

I need an advice to find out the most accurate way to calculate Length of service for employees. taking into consideration the the month is 30 days and 365 days in a year (Law).

for example the start date (Hiring Date) is 20/5/2014 and the last day of work is 18/9/2020 (included),

I thought of YearFrac function but there is only actual/365

Here is what I thought of:

• - using YearFrac function but there is only actual/365 (I not sure if it affects the 30 days in a month rule)
• - using Now function - hiring date divided by 365, or days(18/9/2020,20/5/2014)/365, resort to
• - using datedif function DATEDIF(20/5/2014,18/9/2020,"y")+(DATEDIF(20/5/2014,18/9/2020,"ym")/12)+(DATEDIF(20/5/2014,18/9/2020,"md")/30), or

in Microsoft support calculate-the-difference-between-two-dates- there is a warning " We don't recommend using the DATEDIF "md" argument because it may calculate inaccurate results. "

Thank you

 

[Dave Patton]

What do you require? Days, months, years, ...?

T202009b.xlsm
	B	C	D	E	F	G
1	Hire	End	Days	Months	Years	Years
2	20-May-14	18-Sep-20	2314	75	6.34	6.34
3
3d

Cell Formulas
Range	Formula
D2	D2	=C2-B2+1
E2	E2	=DATEDIF(B2,C2,"M")
F2	F2	=D2/365
G2	G2	=YEARFRAC(B2,C2,3)

 

[jasonb75]

I would be reluctant to provide advice on a method for this without more detailed information. There are many ways that anyone not familiar with the law in your country could interpret the problem.

As a basic example, you say 'Month is 30 days', so that would mean that 12 months is 360 days. Where do the extra 5 days in a year come from?

 

[aliibrahem]

What do you require? Days, months, years, ...?

T202009b.xlsm
	B	C	D	E	F	G
1	Hire	End	Days	Months	Years	Years
2	20-May-14	18-Sep-20	2314	75	6.34	6.34
3
3d

Cell Formulas
Range	Formula
D2	D2	=C2-B2+1
E2	E2	=DATEDIF(B2,C2,"M")
F2	F2	=D2/365
G2	G2	=YEARFRAC(B2,C2,3)

First, thank toy for your response,

In the end, I have to multiple the number of years by employee's salary as a severance pay.

So based on what you provided, I believe than it is sufficient the result in F2 with the salary.

Thank you

 

[Dave Patton]

Without a clear definition and explanation of your challenge, we are just guessing.
Thanks for the feedback and glad you have a solution.

T202009b.xlsm
	B	C	D	E	F	G	H
1	Hire	End	Anniv	Years v1	Years v2	Years v3
2	20-May-14	18-Sep-20	20-May-20	6.34	6.34	6.34
3
4	or	6 years and 4 months		or	6 years and 3 months	6	3
5
3d

Cell Formulas
Range	Formula
D2	D2	=DATE(YEAR(C2),MONTH(B2),DAY(B2))
E2	E2	=(C2-B2+1)/365.25
F2	F2	=YEARFRAC(B2,C2,3)
G2	G2	=DATEDIF(B2,C2,"y")+((C2-D2+1)/30)/12
C4	C4	=DATEDIF(B2,C2,"y")&" years and "&ROUND((C2-D2+1)/30,0)&" months"
F4	F4	=DATEDIF(B2,C2,"y")&" years and " &DATEDIF(B2,C2,"ym")&" months"
G4	G4	=DATEDIF(B2,C2,"y")
H4	H4	=DATEDIF(B2,C2,"ym")

 

[aliibrahem]

Thank you Jasonb75 and Dave Patton for your responses,

When calculating the Severance Pay; to figure out the length of service, one divides the length of service in days by 365 days.

But when adding days, months of 31 days are added as well. I'm not great with time and days formulas in excel.

As you jasonb75 mentioned, 30 × 12 = 360. But the year is considered 365, which made me suspicious. either way, I believe 30-days in a month rule is irrelevant since adding days then dividing the result with with 365.

what made me more suspicious is the results of yearfrac formula, have a look:

Start Date​	End Date​	Days f.​		YearFrac f.​
01/01/2018​	31/12/2018​	365​	=DAYS(C7,B7)+1​	0.997260274​	=YEARFRAC(B7,C7,3)​
01/01/2019​	31/12/2019​	365​	=DAYS(C8,B8)+1​	0.997260274​	=YEARFRAC(B8,C8,3)​
01/01/2020​	31/12/2020​	366​	=DAYS(C9,B9)+1​	1​	=YEARFRAC(B9,C9,3)​
01/01/2021​	31/12/2021​	365​	=DAYS(C10,B10)+1​	0.997260274​	=YEARFRAC(B10,C10,3)​

but when adding a day to the end date argument for the Yearfrac formula I get 1 eg.

YearFrac​
1​	=YEARFRAC(B7,C7+1,3)​
1​	=YEARFRAC(B8,C8+1,3)​
1.002739726​	=YEARFRAC(B9,C9+1,3)​	Leap year​
1​	=YEARFRAC(B10,C10+1,3)​

the result of the yearfrac is 1 (Why? I don't know, It would be great if someone can explain why)

for a leap year 2020, the 1 day extra (to the 365 days rule) will be carried over to the following year.

 

[Dave Patton]

N.B. You never answered the questions but you asked additional questions!

T202009b.xlsm
	A	B	C	D
6	1-Jan-18	31-Dec-18	365	1.00
7	1-Jan-19	31-Dec-19	365	1.00
8	1-Jan-20	31-Dec-20	366	1.00
9	1-Jan-21	31-Dec-21	365	1.00
10
3d

Cell Formulas
Range	Formula
C6:C9	C6	=B6-A6+1
D6:D9	D6	=YEARFRAC(A6,B6)

 

[aliibrahem]

first off, I would like to apologize for any confusion I caused.

the aim I want to achieve is this, if an employee works a full year e.g. from the beginning of 1/1/2020 to the end of 31/12/2020 he/she shall be paid a month salary.
- if he/she gets a monthly salary of $3000 and works a complete year, his/her severance pay is $3000. If works two complete years he/she gets $6000 and so on.

and because the law states that a year is considered 365 days, so I resorted to YEARFRAC( start_date, end_date, [basis] ) with basis 3 = Actual/365
- but I get less than 1 as a result of YEARFRAC function with basis 3 (for a complete year), surprisingly to me, YEARFRAC with basis 0 or omitted = US (NASD) 30/360 yields the correct answer 1.

Start Date	End Date	YEARFRAC
1/1/2018​	12/31/2018​	0.997260274​
1/1/2019​	12/31/2019​	0.997260274​
1/1/2020​	12/31/2020​	1​
1/1/2021​	12/31/2021​	0.997260274​

your previous response, D2/365 I believe solved the issue.

here is a sample

Start Date	End Date	# of days	/365	Severance Pay ($3000 monthly Salary)
01/01/2018​	31/08/2019​	608​	1.665753​	$4997.260274​
01/04/2018​	31/12/2018​	275​	0.753425​	$2260.273973​
01/01/2019​	31/12/2019​	365​	1​	$3000​
01/01/2020​	20/09/2020​	264​	0.723288​	$2169.863014​
01/01/2018​	31/12/2019​	730​	2​	$6000​

Thank you.