Solved

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

Posted on 2003-11-03
5
1,396 Views
Last Modified: 2012-08-14
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..
0
Comment
Question by:jackolim
  • 2
  • 2
5 Comments
 
LVL 69

Accepted Solution

by:
Callandor earned 50 total points
ID: 9672053
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
 
LVL 2

Expert Comment

by:wellilein
ID: 9672147
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
 
LVL 24

Expert Comment

by:R_Rajesh
ID: 9672158
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
 
LVL 24

Expert Comment

by:R_Rajesh
ID: 9672255
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
 
LVL 2

Expert Comment

by:wellilein
ID: 9672335
=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

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now