Longer months with more weeks.

[rjs1943]

I need a formula to find which months have more weeks than others. I get paid every fourth Wednesday. Some months, sometimes 4 or 5, I have to wait 5 weeks between paydays, most other months I wait 4 weeks between paydays. Need a formula to tell me which months has 5 weeks between every 4th Wednesday. The rest of the months will show me 4 weeks between paydays.

Thanks:
Rich

 

[sheetspread]

(forget what I wrote before)

 

[mole999]

if you do something like this
A2 down, 1st of month, drag to extend
b2 down =DATE(YEAR(A2),MONTH(A2),8)-WEEKDAY(DATE(YEAR(A2),MONTH(A2),4))+21
quick test in C2 =B2 and format as ddd
in D3 =B3-B2 gives you a day count between each line
in E3 =D3/7 shows 4 or 5

 

[Tetra201]

If you have some date in cell A1, the following formula will return the fourth Wednesday of the corresponding month:

=WORKDAY.INTL(EOMONTH(A1,-1),4,"1101111")

 

[sheetspread]

If to you a week begins every Monday, then with =DATE(2018,ROW(A1),1+1*7)-WEEKDAY(DATE(2018,ROW(A1),8-4)) in say B1, copied down (date of the first Wednesday in each month) and =EOMONTH(DATEVALUE("12/31/2017"),ROW(A1)-1)+1 in C1 (date of the first of the month), B1-C1 (difference) in D1, you can spot any month with a value >=5 as having the 4th Wednesday occur during the 5th week.

 

[sheetspread]

or just

=WEEKDAY(EOMONTH(DATEVALUE("12/31/2017"),ROW(A1)-1)+1)>4

copied down works too

 

[sheetspread]

Excel 2010
	B	C
1	1/24/2018
2	2/28/2018	35
3	3/28/2018	28
4	4/25/2018	28
5	5/23/2018	28
6	6/27/2018	35
7	7/25/2018	28
8	8/22/2018	28
9	9/26/2018	35
10	10/24/2018	28
11	11/28/2018	35
12	12/26/2018	28
13	1/23/2019	28
14	2/27/2019	35
15	3/27/2019	28
Sheet2

Cell Formulas
Range	Formula
B1	=DATE(2018,ROW(A1),1+4*7)-WEEKDAY(DATE(2018,ROW(A1),8-4))
C2	=B2-B1

 

[sheetspread]

Post #7 just above is my only solution, forget the others I wrote please (they're inaccurate).

 

[Rick Rothstein]

If you don't want to maintain a list of paydays on a worksheet, the following formula will tell you directly how many weeks between the current month's payday and next month's payday (no matter what the current day is in the current month)...

="Weeks between this and next month's paydays: "&(DAY(DATE(YEAR(NOW()),MONTH(NOW())+1,29)-WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW())+1,4)))+(DAY(EOMONTH(NOW(),0))-DAY(DATE(YEAR(NOW()),MONTH(NOW()),29)-WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),4)))))/7

 

[Tetra201]

A slightly shorter one (based on formula from Post # 4):

="Weeks between this and next month's paydays: "&SUM(WORKDAY.INTL(EOMONTH(NOW(),{0,-1}),4,"1101111")*{1,-1})/7