1

I would like to count the occurrence of each day of the week in a month if they are not national holidays.

In more details, I would like to know how many Mondays, Tuesdays, Wednesdays, Thursdays, and Fridays a person has to work in a given month because every day has different business hours and their salary is based on that.

So far I am using the following custom function:

function occurrenceOfSpecificDay(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;
}

With this script, I can return how many Mondays are in September 2019 by inserting =occurrenceOfSpecificDay("Monday", "September", 2019) into a cell, but I do not want to count the day if it is a holiday.

I would like to extend the following condition with an extra argument that returns false if the given day is a Danish holiday:

if(day === checkDay ){
    sumDays ++;
}
1

@tedinoz guided me to a different approach: instead of the custom function, I ended up using NETWORKDAYS.INTL the following way:

  • Mondays: =NETWORKDAYS.INTL("8/1/2019",eomonth("8/1/2019", 0), "0111111")
  • Tuesdays: =NETWORKDAYS.INTL("8/1/2019",eomonth("8/1/2019", 0), "1011111")
  • Wednesdays: =NETWORKDAYS.INTL("8/1/2019",eomonth("8/1/2019", 0), "1101111")
  • Thursdays: =NETWORKDAYS.INTL("8/1/2019",eomonth("8/1/2019", 0), "1110111")
  • Fridays: =NETWORKDAYS.INTL("8/1/2019",eomonth("8/1/2019", 0), "1111011")

Explanation

According to the documentation, the 3rd parameter of NETWORKDAYS.INTL is a 7-character-long sequence of "1"-s and "0"-s, representing the days of the week (the first character is Monday, the next is Tuesday, etc.) where the "1" means "weekend" and the "0" means "weekday".

Since NETWORKDAYS.INTL only counts workdays, it could be used to count specific days by setting the characters representing the other days to "0". Meaning, 0100000 will only count Tuesdays (only if they are not holidays) because the rest of the days are defined as weekends.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.