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.
These are the features that we will aim to incorporate in this calendar aggregator:
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.
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:
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:
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:
IMPORTANT! If there are two different holidays on the same day and you want to see the different holidays in the aggregated view, things get complicated.
You NEED TO combine the rows of the same days together into one row i.e. India - Jan 14 - Pongal; Makal Sankranti
In my case, I just want to know IF THERE IS a holiday, I'm not concerned about what it is. If you would want to know, either do it manually (there's not a lot) or use Python.
Once you have this, key this into Cell A2 but don't confirm the formula yet:
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.
You should have 2 sheets now:
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:
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)
Here's an explanation of this formula:
As VLOOKUP only returns the first result that it found, make sure you combine rows in Database sheet where the holiday's dates are the same for the same country. Else, only the first holiday will appear.
If everything is done correctly, you will see your calendar aggregator now.
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.
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.