How Many Wednesdays in a Month Where Weeknum is OddNum

[brncao]

Trying to figure out the formula for this. Type the following in column A:

Jan 2018
Feb 2018
Mar 2018
...

In column B, the formula will calculate how many Wednesdays there are in that Month where Weeknum is an odd number.
Most months have 2, but a few will have 3.

 

[Rick Rothstein]

In column B, the formula will calculate how many Wednesdays there are in that Month where Weeknum is an odd number.
Most months have 2, but a few will have 3.

Most will have 2, a few 3??? In any given month, there are always four Wednesdays (same for the other days as well) and sometimes there is a fifth Wednesday. I am also not sure how (or why) the weeknum figures into that count. Are you asking for something other than the total number of Wednesday in a given month (if so, please explain in more detail) or did you simply mistype that 2 and 3 above?

 

[brncao]

In a nutshell, if one gets paid every 2 weeks on Wednesdays, calculate the number of paychecks I will receive in that month. My paycheck is on weeknum #1 then #3 , then #5 , etc. They're odd number. However, if there are better alternatives, then I'm all ears.

 

[Rick Rothstein]

In a nutshell, if one gets paid every 2 weeks on Wednesdays, calculate the number of paychecks I will receive in that month. My paycheck is on weeknum #1 then #3 , then #5 , etc. They're odd number. However, if there are better alternatives, then I'm all ears.

Ah, now I see where the odd weeknum fits in. Okay, give this formula a try...

=2+(MONTH(DATE(YEAR(A1),MONTH(A1),8)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),4))+28)=MONTH(A1))

Make sure the cell format remains General (one of the times I did it, the format changed to date).

 

[Dave Patton]

Excel 2010
	D	E	F
1	Wed 03-Jan-18	3	43103
1a

Cell Formulas
Range	Formula
E1	=SUMPRODUCT(--(MONTH({43103;43117;43131;43145;43159;43173;43187;43201;43215;43229;43243;43257;43271;43285;43299;43313;43327;43341;43355;43369;43383;43397;43411;43425;43439;43453})=ROW(A1)))
F1	=D1

Wed Jan 3 2018 is day 43103
Every 14th day is 43117 etc

Copy the formula down
Fill range C1:C12 with January to December

 

[brncao]

Excel 2010

	D	E	F
1	Wed 03-Jan-18	3	43103

<tbody>
</tbody>

1a​

Worksheet Formulas Cell Formula E1 =SUMPRODUCT(--(MONTH({43103;43117;43131;43145;43159;43173;43187;43201;43215;43229;43243;43257;43271;43285;43299;43313;43327;43341;43355;43369;43383;43397;43411;43425;43439;43453})=ROW(A1))) F1 =D1 <tbody> </tbody>

<tbody>
</tbody>

Wed Jan 3 2018 is day 43103
Every 14th day is 43117 etc

Copy the formula down
Fill range C1:C12 with January to December

Thank you for replying. The formula looks very inflexible. I see you hard-coded those serial dates? It doesn't work for any given year but 2018. It also breaks if I start it on a different row or insert a new row.

Ah, now I see where the odd weeknum fits in. Okay, give this formula a try...

=2+(MONTH(DATE(YEAR(A1),MONTH(A1),8)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),4))+28)=MONTH(A1))

Make sure the cell format remains General (one of the times I did it, the format changed to date).

This worked very nicely and is exactly what I needed. Thank you very much!

 

[Dave Patton]

The easiest way to get the correct result is to put the dates for the payment (every 2 weeks) in a range of cells and then calculate the number by month.
This could be extended to as many years that you want.
This approach or this approach converted to hard coded dates yields 26 payment for 2018.

 

[Dave Patton]

Your narrative and the subject title may not describe your actual requirement.

My suggestion yields the number of payrolls by month when your are paid every second week.
The Wednesdays may be even or odd days.

 

[Dave Patton]

My suggestion yields the number of payrolls by month when the payroll is paid every second week.
The first payroll for 2018 is in E2 (Jan 3, 2018); edit if required.
For example, B2 Jan is the Date Jan 1, 2018 which yields 3 payrolls; alternatively, one can look at the table
that shows the results to years 2018-2022."

Excel 2010
	A	B	C	D	E
1	Year	2018	# of Payrolls	Last Payroll in Prior Yr	Wed 20-Dec-17
2		Jan	3	First in 2018	Wed 03-Jan-18
3		Feb	2		2-Feb-23
4		Mar	2		1870
1a

Cell Formulas
Range	Formula
E1	=E2-14
E3	=E2+365*5+31+N("5 for the number of years required")
E4	=E3-E1
B2	=DATE(B1,1,1)
B3	=EDATE(B2,1)
C2	=SUMPRODUCT(--(WEEKDAY(rDates)=4),--(MOD(rNums,14)=0),--(rDates-DAY(rDates)+1=B2))
C3	=SUMPRODUCT(--(WEEKDAY(rDates)=4),--(MOD(rNums,14)=0),--(rDates-DAY(rDates)+1=B3))
D2	="First in "&B1

Named Ranges
Name	Refers To	Cells
rNums	=ROW(!$1:$1870)
'1a'!rDates	=ROW(INDIRECT('1a'!$E$1+1&":"&'1a'!$E$3))

Excel 2010
	H	I	J	K	L	M	N
1	# of Payrolls	Month #	2018	2019	2020	2021	2022
2	January	1	3	3	3	2	2
3	February	2	2	2	2	2	2
4	March	3	2	2	2	2	2
5	April	4	2	2	2	2	2
6	May	5	2	2	2	2	2
7	June	6	2	2	2	3	3
8	July	7	2	3	3	2	2
9	August	8	3	2	2	2	2
10	September	9	2	2	2	2	2
11	October	10	2	2	2	2	2
12	November	11	2	2	2	2	3
13	December	12	2	2	3	3	2
14			26	26	27	26	26
1a

Cell Formulas
Range	Formula
J2	=SUMPRODUCT(--(WEEKDAY(rDates)=4),--(MOD(rNums,14)=0),--(YEAR(rDates)=J$1),--(MONTH(rDates)=$I2))

Named Ranges
Name	Refers To	Cells
rNums	=ROW(!$1:$1870)
'1a'!rDates	=ROW(INDIRECT('1a'!$E$1+1&":"&'1a'!$E$3))

 

[brncao]

Ah, now I see where the odd weeknum fits in. Okay, give this formula a try...

=2+(MONTH(DATE(YEAR(A1),MONTH(A1),8)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),4))+28)=MONTH(A1))

Make sure the cell format remains General (one of the times I did it, the format changed to date).

Thank you Rick. I noticed October 2018 is returning 3 instead of 2. My paycheck is on the 10th and 24th.

It looks like it's calculating even-numbered weeks. I get paid every two weeks starting on Weeknum 1. It resets every year.