# How to calculate the number of fridays in a month in microsoft office excel?

How to calculate the number of fridays in a month in microsoft office excel? Adding the current date, finding number of days/months/years between two dates is simple but if i input a cell as the current month and current year, i want to know the number of fridays in that particular month. The output is shown in another cell in number format form. how can i do that? by adding c codes or VB codes in excel? thanks..
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
You can do it all in Excel.  Start with a cell that has the first day of the month, create another cell that has the last day of the month, then determine what day is the first Friday of the month, and then figure out how many weeks (Fridays) are there between the first Friday and the last day of the month.

If cell A1 has the first day of the month, then create the last day of the month in B1 with the formula "=EOMONTH(A1,0)".  You can find the first Friday of the month using the WEEKDAY and MOD functions.  In C1, put in the formula "=WEEKDAY(A1,2)".  This will result in a 1 if it is Monday, 2 if it is Tuesday, etc.., and 5 if it is a Friday.  In D1, put in the formula "=MOD(C1,5)", which will tell you how many days from Friday the first day of the month is.  Now you can add 7 days to the first day of the month and subtract D1 to get the first Friday of the month (take care of the special case where the first day of the month is Friday).  Combining it all into one formula in cell E1, "=A1+IF(WEEKDAY(A1,2)=5,0,7)-MOD(WEEKDAY(A1,2,5)" gives you the first Friday of the month.  Now calculate the number of days between the first Friday and the end of the month in cell F1: "=B1-E1".  Divide by 7 and take the integer part in cell G1: "=INT(F1/7)".  This tells you how many Fridays are between the first Friday and the end of the month, so add 1 for the first Friday, and you have the answer you are looking for.

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
Using a german version of Excel, I get this:

=WENN(MONAT(A1+(6-WOCHENTAG(A1)+WENN(WOCHENTAG(A1)>6;7;0)))=MONAT(A1+(6-WOCHENTAG(A1)+WENN(WOCHENTAG(A1)>6;7;0))+28);5;4)

in english, it might be

=IF(MONTH(A1+(6-WEEKDAY(A1)+IF(WEEKDAY(A1)>6;7;0)))=MONTH(A1+(6-WEEKDAY(A1)+IF(WEEKDAY(A1)>6;7;0))+28);5;4)

where A1 contains the date to count fridays for. It seems to me that there must be a simpler solution.
Commented:
Try this,

put The month and year (Nov-03) in A1 now paste the formula below to any cell it calculates no of fridays in the specified month. its a array formula so after pasting instead of hitting enter key, hit shift+ctrl+enter (in doing so excel will automatically put curly braces at the beginning and end of the foumula). In the end of the formula "=6)*1" the 6 specifies the day to look for (sunay=1, monday=2...)

=SUM((WEEKDAY(DATE(YEAR(A1),MONTH(A1),(ROW(INDIRECT("1:"&DAY(DATE(YEAR(A1),MONTH(A1)+1,0)))))))=6)*1)
Commented:
or if you want to do it through code try this, displays the result in A15 assuming you put the date in A1

Sub tDate()
Range("A15").FormulaArray = _
"=SUM((WEEKDAY(DATE(YEAR(A1),MONTH(A1),(ROW(INDIRECT(""1:""&DAY(DATE(YEAR(A1),MONTH(A1)+1,0)))))))=6)*1)"
End Sub
Commented:
=ABRUNDEN((DATUM(JAHR(A1);MONAT(A1)+1;1)-1-(A1+(6-WOCHENTAG(A1)+WENN(WOCHENTAG(A1)>6;7;0))))/7;0)+1

or english perhaps

=ROUNDDOWN((DATE(YEAR(A1);MONTH(A1)+1;1)-1-(A1+(6-WEEKDAY(A1)+IF(WEEKDAY(A1)>6;7;0))))/7;0)+1