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
1		Day of First Thursday in Year = Payday	Enter 1st day of Month Desired	Number of Pay Days in the Month			Payroll for the Year 2015
2		8	10/1/2015	3	<< The Number of Pay Days in Oct 2015		1	1/8/2015
3							2	1/22/2015
4							3	2/5/2015
5							4	2/19/2015
6							5	3/5/2015
7							6	3/19/2015
8							7	4/2/2015
9							8	4/16/2015
10							9	4/30/2015
11							10	5/14/2015
12							11	5/28/2015
13							12	6/11/2015
14							13	6/25/2015
15							14	7/9/2015
16							15	7/23/2015
17							16	8/6/2015
18							17	8/20/2015
19							18	9/3/2015
20							19	9/17/2015
21							20	10/1/2015
22							21	10/15/2015
23							22	10/29/2015
24							23	11/12/2015
25							24	11/26/2015
26							25	12/10/2015
27							26	12/24/2015

<COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5"><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Sheet1​

Worksheet Formulas Cell Formula D2 =COUNTIF($H$2:$H$27,">" & EOMONTH($C$2,-1))-COUNTIF($H$2:$H$27,">" &EOMONTH($C$2,0)) E2 ="<< The Number of Pay Days in " & TEXT(C2,"mmm yyyy") H2 =DATE(YEAR(C2),1,B2) <THEAD> </THEAD><TBODY> </TBODY>

<TBODY>
</TBODY>

 

[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!