Solved

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

Posted on 2003-11-03
5
1,406 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is an explanation of a simple data model to help parse a JSON feed
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
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…

777 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