Find the Months that have 5 Fridays in them

take a look at this post
http://www.mrexcel.com/forum/showthread.php?t=375105

Here's a start...not QUITE what your asking for, but should get you going..

if A1 = year
in B1 and filled down to B12

=SUMPRODUCT(--(WEEKDAY(DATE($A$1,ROW(),ROW($A$1:INDEX(A:A,DAY(DATE($A$1,ROW()+1,0))))))=6))

This will give you the count of fridays in each month.
Row 1 = Jan
Row 2 = Feb
etc..

Hope that helps..

Another not quite what was asked for.
Put a year in A1

In B1, put =TEXT(DATE($A$1,ROW(),1),"mmmm")

and in C1=(MONTH(DATE($A$1,ROW(),29)+MOD(6-WEEKDAY(DATE($A$1,ROW(),29)),7)) = ROW())

Drag B1:C1 down to B12:C12
then column C will tell you if there are 5 Fridays in the month shown in column B.

(Filtering column C for TRUE?)

OK, here's a complete solution.
It does require 2 formulas...

The first formula is pretty much the same as the one I posted before, but made more robust. The last one I posted would only work if it was placed in Row 1 and filled down. It would not have worked if you put it in any other row. That is resolved now. So it looks alot longer. Plus it adds a decimal number to the count, so each 5 will be unique and can be ranked..


A1 = Year
B1 =
=SUMPRODUCT(--(WEEKDAY(DATE($A$1,ROWS($A$1:$A1),ROW($A$1:INDEX(A:A,DAY(DATE($A$1,ROWS($A$1:$A1)+1,0))))))=6))+(0.01*ROWS($A$1:$A1))
C1 =
=IF(ISNUMBER(SMALL(IF($B$1:$B$12>=5,$B$1:$B$12),ROWS($A$1:$A1))),TEXT(DATE($A$1,MATCH(SMALL(IF($B$1:$B$12>=5,$B$1:$B$12),ROWS($A$1:$A1)),$B$1:$B$12,0),1),"mmmm"),"")

The formula in C1 is an Array formula that requires CTRL + SHIFT + ENTER
After entering the formula in C1, highlight the cell and press F2
Then press CTRL + SHIFT + ENTER
When entered correctly, the formula will be enclosed in {Brackets}

Again, you can put them anywhere you want, but in the 2nd formula, $B$1:$B$12 refers to the range you put the first formula in.

Here' still another... I took Jonmo1's (#3) recommendation filling Col B (B1:B12) and
created a UDF for Cell C1 -- =FridaysIn(B1:B12) -- So here you are...

The UDF (Paste into a Standard module) is:

Excel 2003

WOW, looking back on this -- Although it works.. it is a programming disaster.. I've broken most rules of programming. Need I say more? It could be cleaned up and later have a bit more merit, but not till then.
Jim

Well, if you're going to go UDF, you might as well do the whole thing in the UDF, and not depend on the helper column B

This uses the formula in the thread suggested by TexasaLynn
http://www.mrexcel.com/forum/showthread.php?t=375105


Then you just use
=fivefridaymonths(2009)
returns
Jan, May, Jul, Oct

Thank you all for the time that you put in on this post, jonmo1 that was it and what I was looking for. Still curious though if it can be done within one formula without helper columns.

Thanks Again Everyone

Here's a single formula solution, however they won't be put nicely at the top of the collumn...
If March has 5, it will show up in row 3
If June has 5, it will show up in row 6
etc...

=IF(4+(DAY(DATE($A$1,ROWS($A$1:$A1),1)+34)< WEEKDAY(DATE($A$1,ROWS($A$1:$A1),1)-6))=5,TEXT(DATE($A$1,ROW(),1),"mmmm"),"")