Calculate Remaining Paychecks Before Year End

[CPAExcelsior]

Hello Mr. Excel Community,

I am attempting to calculate the number of remaining paychecks for an individual based on a paycheck date and pay frequency. I currently have my formulas set up as below:
Paycheck Date: Enter Manually (e.g. 9/15/2022)
Pay Frequency: Enter from drop down (e.g. semi-monthly)
Total Paychecks Remaining: =INT((DATE(YEAR(B4),12,31)-B4)/14)
This kicks out 7 paychecks remaining based on a bi-weekly pay period.

The formula for total paychecks remaining is based on 14 days in a bi-weekly pay period. I could easily create a formula based on the pay frequency to list 7 if weekly or 14 if bi-weekly. But, this formula breaks down as both semi-monthly and monthly frequencies do not have the same number of days during each period. I considered creating a formula based on frequency that would list the total annual pay periods (e.g. weekly = 52, bi-weekly - 26, semi-monthly = 24, monthly = 12). However, I cannot figure a way to base my Total Paychecks Remaining on the total paychecks in a given year.

Is anyone aware of how I can calculate this without manually counting pay periods on a calendar? We work with many individuals who all have varying pay frequencies and are paid on variable days of the week.

 

[JamesCanale]

MrExcelPlayground14.xlsx
	A	B	C
1	Pay Date	Type	Periods Left
2	9/15/2022	Weekly	15
3	9/15/2022	Biweekly	7
4	9/15/2022	Semimonthly (15, last)	7
5	9/15/2022	Semimonthly (1,15)	6
Sheet13

Cell Formulas
Range	Formula
C2:C5	C2	=LET(a,SEQUENCE((DATE(YEAR(A2),12,31)-A2),,A2+1),b,INT(ROWS(a)/7),c,INT(ROWS(a)/14),d,ROWS(FILTER(a,(DAY(a)=15)+(DAY(a)=DAY(DATE(YEAR(DATE(YEAR(a),MONTH(a),28)+10),MONTH(DATE(YEAR(a),MONTH(a),28)+10),1)-1)))),e,ROWS(FILTER(a,(DAY(a)=15)+(DAY(a)=1))),IF(B2="Weekly",b,IF(B2="Biweekly",c,IF(B2="Semimonthly (15, last)",d,e))))

 

[CPAExcelsior]

Thank you so much for your help, James! So if I understand correctly, any semimonthly pay date between the 1st and 15th will use the option "Semimonthly (1, 15)" and any semimonthly pay date between the 15th and 31st will use the option "Semimonthly (15, last)" - correct?

I did update the formula slightly to include individuals paid on a monthly basis. See below where your A2 is my B4 and your B2 is my B5. Does that appear correct?

=LET(a,SEQUENCE((DATE(YEAR(B4),12,31)-B4),,B4+1),b,INT(ROWS(a)/7),c,INT(ROWS(a)/14),d,ROWS(FILTER(a,(DAY(a)=15)+(DAY(a)=DAY(DATE(YEAR(DATE(YEAR(a),MONTH(a),28)+10),MONTH(DATE(YEAR(a),MONTH(a),28)+10),1)-1)))),e,ROWS(FILTER(a,(DAY(a)=15)+(DAY(a)=1))),f,12-MONTH(B4),IF(B5="Weekly",b,IF(B5="Biweekly",c,IF(B5="Semimonthly (15-last)",d,IF(B5="Semimonthly (1-15)",e,f)))))

 

[JamesCanale]

"Last" isn't the 31st. It could be the 30,29, or 28th. It's the day before the first of the next month. (or really tediously - it's the day before, the first of the month that is 10 days after the 28th of the current month. EOMONTH doesn't work in arrays.)

12-month(b4) looks right to me for monthly. A nice simple formula.