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..
jackolimAsked:
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.

CallandorCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
wellileinCommented:
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.
0
R_RajeshCommented:
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)
0
R_RajeshCommented:
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
0
wellileinCommented:
=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

is already a bit shorter.

DATE(YEAR(A1);MONTH(A1)+1;1)-1 is the last day of the month
A1+(6-WEEKDAY(A1)+IF(WEEKDAY(A1)>6;7;0)) is the first friday of the month
The difference between them divided by 7 plus the first friday is the number of fridays in the month.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.