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.
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.
Now copy and paste the following code into the editor and save it. You will be calling this function directly from your sheet.
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.
Back in the sheet navigate to the column that will contain the result of the working days calculation and enter the formula
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.
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.
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.