Automated Multi-country Calendar Aggregator with Google Sheets

When working on a campaign/project that consists of teams from multiple regions or products/services available in multiple regions, it's useful to have the calendar of each country readily in view. 

It can be useful for arranging a special promotion, arranging for team member's vacations and understanding impact to your campaign's performance.

In this post, we'll build a calendar aggregator for APAC market that is viewable on one single Google Sheet, leveraging on TimeandDate.com's public data.

I chose APAC due to its highly-unique culture and history of each country, hence the holidays are very different between countries.

 

The Goal

These are the features that we will aim to incorporate in this calendar aggregator:

• Replicable (easy to update from year-to-year)
• A single view of all countries
• Simple, light and doesn't break Google Sheets

Here's a snapshot of what we'll achieve.

This example doesn't include some countries as my day-to-day work did not interact with those countries, such as China and Brunei.

 

Getting Data from TimeandDate.com

The first step is to extract data from TimeandDate.com.

Let's start a simple one with Australia.

The URL for Australia's calendar is https://www.timeanddate.com/holidays/australia. As you can see, changing the final part of the URL to any country's name will direct you to the respective country's calendar.

We'll be pulling the tabular data below into Google sheet.

Create a new sheet on Google Sheet. Set your sheet in this manner below (colours optional):

On cell A1, I've set up a Data Validation (Data > Data Validation) with all the country names.

Next, key in this formula in Cell A2:

="https://www.timeanddate.com/holidays/"&lower(substitute(A1," ","-"))

That creates the dynamic URL for each country. Next, type this formula in Cell A5:

=QUERY(IMPORTHTML(A2,A3,A4),"SELECT Col1, Col2, Col3, Col4, Col5 WHERE NOT Col4 = 'Observance'")

IMPORTHTML extracts the first table from the HTML of the URL that we've passed. The QUERY formula removes data where the Holiday Type is "Observance", as that is usually not a Public Holiday.

With some formatting, you should get this:

 

Create a Collated Calendar Dataset

Now that we have the calendar for Australia, how do we collate the calendar data for all markets?

The best way would be to use Python coupled with Google Sheets to repeatedly copy, paste to another sheet, change the country on Cell A1 and repeat.

You can follow my guide on linking Python with Google Sheets to execute this.

Else, you can be hardworking and do all that manually with your mouse/trackpad, Ctrl+C and Ctrl+Shift+V.

Your aim is to get something like this:

Once you have this, key this into Cell A2 but don't confirm the formula yet:

=B2:B&C2:C

Press Ctrl+Shift+Enter and Enter again to create an ArrayFormula. Now you will have a unique combination of country and date that will serve as your lookup ID.

 

Creating the Aggregated Calendar View

You should have 2 sheets now:

1. The calendar generator (name it "Calendar Generator")
2. The collated "database" of holidays for all countries (name it "Database")

Now create a third sheet that will be your calendar view.

Set the sheet up this way:

Here are a few things I've done:

1. Row 1 has all the countries I have in my "database" (the row is frozen).
2. Column A has all the dates from Jan 1 to Dec 31. Use =A2 + 1 and drag it all the way down.
3. The rest of the cells have a conditional formatting applied to colour the cell if there is content.

Believe it or not, once you have all the three steps done correctly, you're only ONE formula away from finishing your calendar.

Place this complicated formula in B2, and then ArrayFormula it (Ctrl+Shift+Enter, Enter)

=iferror(VLOOKUP(B$1:N$1&$A2:$A,Database!$A:$F,5,false),"")

Here's an explanation of this formula:

1. The VLOOKUP takes in B$1:N$1&$A2:$A as the first argument. What this does is it combines the countries in Row 1 and the dates in Column A to create the unique ID. Row 1 and Column A are frozen ($ symbol) in the formula. Once you ArrayFormula it, each cell essentially has the unique ID as its first argument.
2. The second VLOOKUP argument is the Database sheet, where Column A has the unique lookup ID (combination of country and date)
3. The third VLOOKUP argument is the 5th column that is the Holiday's name
4. The final VLOOKUP argument is "false" because the data is not sorted in my case
5. IFERROR to render the cell blank if there is no match (i.e. no holiday on that day in that country)

If everything is done correctly, you will see your calendar aggregator now.

 

Adding a Day Tracker

This is an optional step to add a highlight on the row where the date is today. Here's how it looks on March 11.

To do that, go to Cell B2 and navigate to Format > Conditional Formatting...

In the field "Apply to range", click the 3x3 grid icon at the right and then highlight your data range except for the first row and first column.

After that, select "Custom formula" in the drop-down and key in the following in the textbox below.

=(today()=$A2)

You can apply whatever formatting you want below. Your setting should look something like this.

You now have a day-tracking highlight that moves every day so that your users know where to look.

The technique introduced here can be used to visualise any date-related data and not just limited to holidays. You can add any form of data into the Database sheet and the one-sheet multi-calendar view should still work.

This calendar is very easy to make with the exception of the Database step. If you have any better methods to do this, please suggest in the comment section below.

Happy Calendar-ing!