Google Sheets – Count Occurrence of Each Day of the Week Excluding Public Holidays

google sheetsgoogle-apps-scriptgoogle-sheets-custom-functiongoogle-sheets-dates

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 ++;
}

Best Answer

@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.

Code

function setUsers() {  
  var date = new Date(), day = date.getDay(), hour = date.getHours();
  var monday = 1, friday = 5;
  if((day == monday && hour == 8) || (day == friday && hour == 14)) {
    var sProp = PropertiesService.getScriptProperties();
    if(!sProp.getProperty('day')) { 
      sProp.deleteAllProperties();
      sProp.setProperty('day', day);
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      switch(day) {
        case monday:
          ss.addEditors(ss.getViewers());
          break;
        case friday:
          var editors = ss.getEditors(); 
          for(var i = 0, iLen=editors.length; i < iLen; i++) {
            var user = editors[i];
            ss.removeEditor(user);
            ss.addViewer(user);
          }
          break;
      }      
    }    
  }
  return;
}

Trigger

enter image description here

Explained

The trigger is only fired when it's a Monday or a Friday and only on those specific times. After that it will check whether it hasn't been triggered once before, by looking up in the scripts memory. If nothing is found for that day, then delete all other entries and create a new one. This way, if the script is triggered again, it will find an entry and skip to return. The switch statement makes it possible to switch between the days and the different actions to make. On Mondays, the existing viewers need to be added as editors. This can be done via a batch operation (at once). On Fridays the editors need to be removed first in order to add them as viewers.

Notes

I've tested it against two of my own accounts and it worked. As far as I know, no messages are being send about the user being added as viewer or as an editor (which is the case when you do that manually). In the editor mode, the viewer mode is being enforced automatically. This isn't the case when the user is in the viewer mode. There the user needs to refresh the window (F5).

If you the set trigger on a 5 minutes interval, then in theory the time frame can be missed by 5 minutes. If you choose to pick the 1 minute interval, then this will be minimized to 1 minute.

Another option would be to create a trigger each and every time. This way the set time can be programmatically triggered (even thought the triggers are bound to be executed give or take 15 minutes, see reference²).

Add the code under Tools > Script editor and press the bug button to authenticate the script. Furthermore, create a trigger by selecting Resources > Current project's triggers.

Setback

There's one huge setback to this solution however. Everyone, added as an editor, will be able to see and change the code and there's no way to avoid that, see reference¹.

Reference

Google Sheets – How to Calculate the Percentage of a Year Up to a Date

Here is a shorter formula that is equivalent to yours, using yearfrac:

=yearfrac(date(year(A1), 1, 1), A1, 1)

However, the formula you have is inconsistent with your description, since it returns 0 for 1-1-2015, representing that at the beginning of that day, no time has elapsed since the beginning of the year. To have 1/365 for 1-1-2015 and 1 for 12-31-2015, add 1 to the date, to calculate each day's end:

=yearfrac(date(year(A1), 1, 1), A1+1, 1)