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

JJKinVA

New Member
Joined
Dec 10, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Per your request: a mega-formula ;)
Excel 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")))
 
Upvote 0
Solution
Thanks, Tetra201 !! I'm still breaking down and deciphering the logic/calculative path, but it sure works great ! Thx, again !!!
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top