I would like to find the number of Fridays for a specific month via function in Google Spreadsheets.
For example, for January 2014 the value would be 5
and for February 2014 the value would be 4
.
How can I do that?
This is how to do that with Google Apps Script.
function specificDays(dayName, monthName, year) {
// set names
var monthNames = ["January", "February", "March",
"April", "May", "June",
"July", "August", "September",
"October", "November", "December"
];
var dayNames = ["Sunday", "Monday", "Tuesday", "Wednesday",
"Thursday", "Friday", "Saterday"
];
// change string to index of array
var day = dayNames.indexOf(dayName);
var month = monthNames.indexOf(monthName)+1;
// determine the number of days in month
var daysinMonth = new Date(year, month, 0).getDate();
// set counter
var sumDays=0;
// iterate over the days and compare to day
for(var i=1; i<=daysinMonth; i++) {
var checkDay = new Date(year, month-1, parseInt(i)).getDay();
if(day == checkDay) {
sumDays ++;
}
}
// show amount of day names in month
return sumDays;
}
Add the script via Tools>Script editor in the menu. Save the script and you're on the go !!
I've created an example file for you: Amount of Day Names in Month
=NETWORKDAYS.INTL(DATE(2017,10,1),EOMONTH(DATE(2017,10,1),0),"1111011")
NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
weekend - [ OPTIONAL - 1 by default ] - A number or string representing which days of the week are considered weekends.
String method: weekends can be specified using seven 0’s and 1’s, where the first number in the set represents Monday and the last number is for Sunday. A zero means that the day is a work day, a 1 means that the day is a weekend. For example, “0000011” would mean Saturday and Sunday are weekends.
Assuming the January 2014 is in A1 and the number of day of week is in B1 (Friday is 6):
=CEILING((DATEDIF(A1,EDATE(A1,1), "D")-MOD(B1-WEEKDAY(A1)+7, 7))/7, 1)
Quite simpler I think
I've split it up by looking at the first (likely partial) week:
IF(6>=WEEKDAY(A1),1,0)
Then the number of whole weeks:
QUOTIENT(DAY(EOMONTH(A1,0))-(7-WEEKDAY(A1)+1),7)
Then the last partial or empty week:
IF(MOD(DAY(EOMONTH(A1,0))-(7-WEEKDAY(A1)+1),7)>=6,1,0)
Giving us the whole formula:
=IF(3>=WEEKDAY(A1),1,0)+QUOTIENT(DAY(EOMONTH(A1,0))-(7-WEEKDAY(A1)+1),7)+IF(MOD(DAY(EOMONTH(A1,0))-(7-WEEKDAY(A1)+1),7)>=6,1,0)
This places reliance on the accuracy of EOMONTH()
, which is likely a good call - should be safer against leap years etc.
Set A1 to be the first day of the month in question. Then:
=CEILING((DAY(EOMONTH(A1, 0)) - MOD(6 - WEEKDAY(A1), 7)) / 7)
This works for any day of the week, just change the 6
after MOD(
to the corresponding day of the week.
How it works:
6 - WEEKDAY(A1)
This counts the days between the first day of the month and the nearest Friday. However, we want to just look for the number of days until the next Friday.
MOD(6 - WEEKDAY(A1), 7)
This returns the number of days between the first day of the month and the first Friday of the month.
DAY(EOMONTH(A1, 0))
This returns the day number of the last day of the month. In other words, the total number of days in the month.
DAY(EOMONTH(A1, 0)) - MOD(6 - WEEKDAY(A1), 7)
Subtract the days between the first of the month and the first Friday of the month from the total days in the month. This returns the total number of days from (and including) the first Friday of the month and the end of the month.
DAY(EOMONTH(A1, 0)) - MOD(6 - WEEKDAY(A1), 7)) / 7
Divide this number by 7 which tells you how many weeks between the first Friday of the month and the end of the month. For example, if February has 28 days and the
CEILING((DAY(EOMONTH(A1, 0)) - MOD(6 - WEEKDAY(A1), 7)) / 7)
Round the number of weeks up to determine the total number of Fridays.
For example, if the first of July is a Wednesday, there are two full days in July before the first Friday in July. July has 31 days, so there are 29 days from (and including) the first Friday. A 29 day range, starting on the first Friday, allows 4 full weeks, plus an additional day. Since we started on a Friday, the additional day is also a Friday.
Assuming the month/year is in A1 and if with a specific date that that is the 1st, please try:
=IF(AND(WEEKDAY(EOMONTH(A1,0))=6,EOMONTH(A1,0)-EOMONTH(A1,-1)=29),5,IF(OR(AND(EOMONTH(A1,0)-EOMONTH(A1,-1)=31,WEEKDAY(A1)>3),AND(EOMONTH(A1,0)-EOMONTH(A1,-1)=30,OR(WEEKDAY(A1)=5,WEEKDAY(A1)=6))),5,4))
This formula will also work:
=QUOTIENT(DAY(EOMONTH(A1, 0)) - WEEKDAY(EOMONTH(A1, 0)) + 6, 7) + IF(WEEKDAY(EOMONTH(A1, 0)) > 5, 1, 0)
It is much cleaner if you are looking to count the number of Sundays as it can be simplified to:
=QUOTIENT((DAY(EOMONTH(A1, 0)) - WEEKDAY(EOMONTH(A1, 0)) + 7), 7)