Calculate number of paydays in a month?

[charlesstricklin]

If I give Excel a date (say, January 1, 2015) and assuming I get paid every two weeks on Thursdays, how would I go about finding the number of paychecks per month? (I know it'll either be 2 or 3, of course.)

 

[jim may]

Don't we need to know the date of the First Thursday in each year you are paid?

 

[Matty]

The following simply counts the number of Thursdays in a given month, divides this number by 2 and then rounds the retuned value:

=ROUND(INT((WEEKDAY(A1-5)+DATE(YEAR(A1),MONTH(A1)+1,0)-A1)/7)/2,0)

To Jim's point, this may or may not be what is needed.

Matty

 

[charlesstricklin]

Don't we need to know the date of the First Thursday in each year you are paid?

Thursday, January 1, 2015 will be a payday, so we can use that. And it'll be every other week after that, so I can't assume the first Thursday of 2016 or after will be a payday.

 

[OTOTO]

Hi, I did this for you on a spreadsheet. I can email it to you as an attachment?

 

[jim may]

Excel 2012

	A	B	C	D	E	F	G	H
<< The Number of Pay Days in Oct 2015

<COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5"><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Day of First Thursday in Year = Payday[/TD]
[TD="align: center"]Enter 1st day
of Month
Desired[/TD]
[TD="align: center"]Number of
Pay Days
in the Month[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Payroll for the Year 2015[/TD]
[TD="align: center"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"]8[/TD]
[TD="bgcolor: #ffff00, align: center"]10/1/2015[/TD]
[TD="bgcolor: #c5d9f1, align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1/8/2015[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1/22/2015[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2/5/2015[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2/19/2015[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3/5/2015[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]3/19/2015[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]4/2/2015[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]4/16/2015[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]4/30/2015[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5/14/2015[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]5/28/2015[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]6/11/2015[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]6/25/2015[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]7/9/2015[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]7/23/2015[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]8/6/2015[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]8/20/2015[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]9/3/2015[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]9/17/2015[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]10/1/2015[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]10/15/2015[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]10/29/2015[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]11/12/2015[/TD]

[TD="align: center"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]11/26/2015[/TD]

[TD="align: center"]26[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]12/10/2015[/TD]

[TD="align: center"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]12/24/2015[/TD]

</TBODY>

Sheet1​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<THEAD>[TR="bgcolor: #dae7f5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</THEAD><TBODY>[TR]
[TH="width: 10, bgcolor: #dae7f5"]D2[/TH]
[TD="align: left"]=COUNTIF($H$2:$H$27,">" & EOMONTH($C$2,-1))-COUNTIF($H$2:$H$27,">" &EOMONTH($C$2,0))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #dae7f5"]E2[/TH]
[TD="align: left"]="<< The Number of Pay Days in " & TEXT(C2,"mmm yyyy")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #dae7f5"]H2[/TH]
[TD="align: left"]=DATE(YEAR(C2),1,B2)[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[OTOTO]

column A: Enter 1/1/2015 into cell A2, drag down to fill sequence through 12/31/2015
column B: Month(A2) and drag down
Column C: in C2 enter Thursday and drag down to fill in all the days of the week
Column D: =Isodd(Day(A2))

This is the data. Now set up a table
column F: Jan-Dec
column G: number 1-12 (to correspond with the month)
column H: =COUNTIFS($B$2:$B$366,G2,$C$2:$C$366,"*Thursday*",$D$2:$D$366,"TRUE")

 

[jim may]

Change My Cell B2 from 8 to 1 --- It should work!!

 

[OTOTO]

Nice solution Jim. I like the entry cell. You answered his questions for sure!