Calculate number of remaining biweekly pay periods

[ImRockDoc]

I am federal employee. I have a need to figure out how many pay periods remain in a year based on the present date.. Depending on when the leave year ends from the previous year dictates when the first biweekly pay period begins (see Federal Employee Leave Year Beginning and Ending Dates). As such there can be 26 or 27 pay periods. Thanks in advance,

Chuck

 

[Saba Sabaratnam]

Hi Chuck,

Given the layout of my worksheet, enter the following formula in G2 and copy it down.

=INT((DATE(YEAR(F2),12,31)-F2)/14+1)

Kind regards

Saba

 

[ImRockDoc]

Thanks! That was so quick.

 

[ImRockDoc]

Ok the start of the pay periods change. Actually this does not work. Using your example on 10/7/21 should be 6 if we start on the start date by year.

Leave Year	Leave Year Beginning Date
2012	1-Jan-12
2013	13-Jan-13
2014	12-Jan-14
2015	11-Jan-15
2016	10-Jan-16
2017	8-Jan-17
2018	7-Jan-18
2019	6-Jan-19
2020	5-Jan-20
2021	3-Jan-21
2022	2-Jan-22
2023	1-Jan-23
2024	14-Jan-24
2025	12-Jan-25
2026	11-Jan-26
2027	10-Jan-27
2028	9-Jan-28
2029	7-Jan-29
2030	6-Jan-30

 

[Saba Sabaratnam]

Hi Chuck,

I tested it using 1 Jan 2012.

It shows 27 pay periods and on 1 July 2012, 13 pay periods left. They reconcile with detailed cal on the left side.

Kind regards

Saba

 

[ImRockDoc]

Oh you are writing day month year. Thanks.

 

[Saba Sabaratnam]

Yes we use dd/mm/yyyy here

 

[ImRockDoc]

Can you do the same for years starting with Oct 1 and ending with sep 30 of the next year?