Calculate Working Days in a Google Sheet

Published on 12th of April 2020 by Julian

If you want to count the number of working days between two dates, Google Sheets has the NETWORKDAYS and NETWORKDAYS.INTL functions. Using these functions are quite onerous as it requires that you manually specify holidays and in the case of NETWORKDAYS.INTL you must also specify weekends. The problem is compounded the more countries and subdivisions you need to support. Luckily it doesn't need to be this way as you can use Holiday Oracle's business-days API endpoint to calculate working days for a given location exclusive of holidays and weekends.

In this example we will create a simple Google Sheet where each row will consist of location data, start and end dates. For each row we will create a column that calculates the number of working days for the start and end date. To begin with ensure that you have a Holiday Oracle API account as you will need your API token to call the API.
Google Sheets Calculate Working Days

Google sheets will automatically attempt to apply date formating to any column where you enter in a date which may result in errors. Be sure to set the formatting of your date columns to "Plain text".
Google Apps Script

To call the Holiday Oracle API we are going to create a custom function in a Google Apps script that will accept a country code, start date, end date and optional subdivision code. Open up the script editor via the tools menu.
Google Apps Script Editor
Now copy and paste the following code into the editor and save it. You will be calling this function directly from your sheet.

                                  
function calcBusinessDays(country, subdivision, date1, date2) {
    if (!country || !date1 || !date2) {
        return '';
    }

    var apiToken = "INSERT YOUR TOKEN";
    var url = "https://holidayoracle.io/api/v1/date/business-days?date1=" + date1 + "&date2=" + date2 + "&country=" + country + "&api_token=" + apiToken;
    if (subdivision) {
        url += "&subdivision=" + subdivision;
    }
    var response = UrlFetchApp.fetch(url);
    var data = JSON.parse(response.getContentText());
    return data.data.business_days;
}
                                  
                                

The script will send a request to the API requesting summary information regarding the days for the specified period. The summary information will be a JSON structure similar to the following.

                                  
{
"data": {
    "business_days": 2,
    "weekend_days": 2,
    "total_holidays": 4,
    "weekday_holidays": 2
},
"status": "success"
}

Note that you can find the complete script at https://github.com/OffChainData/holiday-oracle-gapps-scripts. Be sure to insert your API token where it states "INSERT YOUR TOKEN" in the code.

Calling the Google Apps custom function from your sheet

Back in the sheet navigate to the column that will contain the result of the working days calculation and enter the formula

=calcBusinessDays(A2,B2,C2,D2)
Substitute the column references in the formula above with the columns that are applicable for your sheet remembering that the first parameter is the country code, followed by subdivision code, start date and end date. Note that dates must be in YYYY-MM-DD format otherwise you will get an error.
Google Apps Script Editor

You'll notice that in our example of Australia, NSW (AU and NSW) for the dates of the 9th of April to the 14th of April the custom formula returns 2 days. There are 6 days during this period, but 2 days make up the weekend and a further 2 days are public holidays due to Easter. Google Apps Script Editor
As clearly demonstrated in this simple scenario, our global holiday date prediction service offers a significant benefit over having to manually maintain a list of holidays and weekend data for several geographical regions. Sign Up for a free trial to get started with our service.