Another Q re: US Fed Govt Bi-Weekly Pay Periods

[JJKinVA]

The topic has been explored several times, but I couldn't seem to find a non-table-lookup, non-VBA way to calculate/determine which pay period a given date falls in.

For the US Fed Govt, 26 bi-weekly pay periods are typical, but occasionally a 27th PPd occurs in a year. Officially, "A leave year begins on the first day of the first full biweekly pay period in a calendar year. A leave year ends on the day immediately before the first day of the first full biweekly pay period in the following calendar year. 27 pay periods occur when the prior leave year ends on a Saturday at the beginning of a 365-day calendar year"

** I wish to simply enter a calendar date (from Jan 01, 2012 and later), and return the PPd number in that year. **

E.g. Enter A1 = "May 23, 2017", and then B1 returns "10" (or 2017.10, or something equivalent).

2012, 2023, and 2034 beginning PPd #1 on Jan 1 seem like solid reference dates to work from, but I can't find the formulaic logic to create a single generalized working formula. Even if a mega-formula :- )

FYI, the following summarizes the Start, End, and # PPds per year.

Year Begin End # PPd
2012 Jan 1, 2012 Jan 12, 2013 27
2013 Jan 13, 2013 Jan 11, 2014 26
2014 Jan 12, 2014 Jan 10, 2015 26
2015 Jan 11, 2015 Jan 9, 2016 26
2016 Jan 10, 2016 Jan 7, 2017 26
2017 Jan 8, 2017 Jan 6, 2018 26
2018 Jan 7, 2018 Jan 5, 2019 26
2019 Jan 6, 2019 Jan 4, 2020 26
2020 Jan 5, 2020 Jan 2, 2021 26
2021 Jan 3, 2021 Jan 1, 2022 26
2022 Jan 2, 2022 Dec 31, 2022 26
2023 Jan 1, 2023 Jan 13, 2024 27
2024 Jan 14, 2024 Jan 11, 2025 26
2025 Jan 12, 2025 Jan 10, 2026 26
2026 Jan 11, 2026 Jan 9, 2027 26
2027 Jan 10, 2027 Jan 8, 2028 26
2028 Jan 9, 2028 Jan 6, 2029 26
2029 Jan 7, 2029 Jan 5, 2030 26

TIA,
-- JJKinVA

 

[Tetra201]

Per your request: a mega-formula

=IF(INT((WORKDAY.INTL(DATE(YEAR(A1),12,32),-1,"1111110")+6+7*ISEVEN(WORKDAY.INTL(DATE(YEAR(A1),12,32),-1,"1111110")-"2012-1-1")-A1)/14)<26+AND(SUMPRODUCT(WORKDAY.INTL(DATE(YEAR(A1),{12,1},{32,0}),{-1,1},"1111110"),{1,-1})=364,ISEVEN(WORKDAY.INTL(DATE(YEAR(A1),12,32),-1,"1111110")-"2012-1-1")),26+AND(SUMPRODUCT(WORKDAY.INTL(DATE(YEAR(A1),{12,1},{32,0}),{-1,1},"1111110"),{1,-1})=364,ISEVEN(WORKDAY.INTL(DATE(YEAR(A1),12,32),-1,"1111110")-"2012-1-1"))-INT((WORKDAY.INTL(DATE(YEAR(A1),12,32),-1,"1111110")+6+7*ISEVEN(WORKDAY.INTL(DATE(YEAR(A1),12,32),-1,"1111110")-"2012-1-1")-A1)/14),26+AND(SUMPRODUCT(WORKDAY.INTL(DATE(YEAR(A1)-1,{12,1},{32,0}),{-1,1},"1111110"),{1,-1})=364,ISEVEN(WORKDAY.INTL(DATE(YEAR(A1)-1,12,32),-1,"1111110")-"2012-1-1")))

 

[JJKinVA]

Thanks, Tetra201 !! I'm still breaking down and deciphering the logic/calculative path, but it sure works great ! Thx, again !!!

 

[Tetra201]

You are welcome.