Biweekly pay periods between two dates (hire date and termination date)

[marshak]

Hi, How can I find the number of biweekly pay periods between two dates?

Thanks so much in advance!

EDITED Headcount Report March 2018 - Feb 2022 (Present).xlsx
	E	F		H			K
1	Start Date	Job Title		Exempt Y/N			Termination Date
2	02/22/18			N			10/10/19
3	01/16/12	SR. ACCOUNTING CLERK		N			03/25/22
4	03/25/13	MQS-INSPECTOR		N			02/28/18
5	02/16/10	PRODUCTION SUPERVISOR		N			03/25/22
6	12/01/14	MATERIAL HANDLER		N			03/25/22
7	09/16/19			N			02/26/20
8	07/20/15			N			05/03/19
9	09/27/10			N			05/28/20
10	12/10/01			N			02/22/19
11	04/09/19			N			01/20/21
12	03/09/20	TECHNICIAN		N			06/30/22
13	09/15/97	ASSEMBLER		N			02/18/22
14	08/20/19			N			09/17/20
15	11/26/18	INSPECTOR LEVEL 3		N			02/18/22
16	04/17/17	TECHNICIAN		N			03/25/22
17	07/22/20			N			09/15/21
18	11/18/19	ASSEMBLER		N			03/25/22
19	09/30/19			N			08/03/20
20	01/04/99	INSPECTOR		N			03/25/22
1_Current Head Count

 

[alansidman]

Power Query solution. You can round up or down in Excel as you did not provide an expected result, I did not know which way to go.

​			​	​

Book1
	A	B	C	D	E
1	Start Date	Job Title	Exempt Y/N	Termination Date	Total Bi-Weeks
2	2/22/2018		N	10/10/2019	42.5
3	1/16/2012	SR. ACCOUNTING CLERK	N	3/25/2022	265.7857143
4	3/25/2013	MQS-INSPECTOR	N	2/28/2018	128.6428571
5	2/16/2010	PRODUCTION SUPERVISOR	N	3/25/2022	315.7142857
6	12/1/2014	MATERIAL HANDLER	N	3/25/2022	190.7857143
7	9/16/2019		N	2/26/2020	11.64285714
8	7/20/2015		N	5/3/2019	98.78571429
9	9/27/2010		N	5/28/2020	252.2142857
10	12/10/2001		N	2/22/2019	448.7857143
11	4/9/2019		N	1/20/2021	46.57142857
12	3/9/2020	TECHNICIAN	N	6/30/2022	60.21428571
13	9/15/1997	ASSEMBLER	N	2/18/2022	637.2857143
14	8/20/2019		N	9/17/2020	28.14285714
15	11/26/2018	INSPECTOR LEVEL 3	N	2/18/2022	84.28571429
16	4/17/2017	TECHNICIAN	N	3/25/2022	128.7857143
17	7/22/2020		N	9/15/2021	30
18	11/18/2019	ASSEMBLER	N	3/25/2022	61.28571429
19	9/30/2019		N	8/3/2020	22
20	1/4/1999	INSPECTOR	N	3/25/2022	605.7857143
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"Termination Date", type date}}),
    #"Inserted Date Subtraction" = Table.AddColumn(#"Changed Type", "Subtraction", each Duration.Days([Termination Date] - [Start Date]), Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Inserted Date Subtraction", "Total Bi-Weeks", each [Subtraction]/14),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Total Bi-Weeks"})
in
    #"Removed Other Columns"