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

JJKinVA

New Member
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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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")))``

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

You are welcome.

Replies
3
Views
292
Replies
2
Views
498
Replies
4
Views
500
Replies
11
Views
399
Replies
1
Views
135

1,221,092
Messages
6,157,886
Members
451,448
Latest member
SAM1961

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.

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

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