Ping Shiuan Chua
  • About
  • Blog
  • Projects
  • PSD Studio
  • Tools
    Keyword Cluster Keyword Grouping Broken Links Check Term-frequency Web Scraper
Google Sheets or Excel

Automated Multi-country Calendar Aggregator with Google Sheets

16 Mar 2018

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.

Multi-country calendar aggregator on Google Sheets

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.

Australia's holiday and observances from timeanddate.com

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

Initial setup to create calendar in Google Sheets

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

All country names calendar

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:

Calendar for Australia in Google Sheets

 

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:

Use Python to repeatedly Copy and Pasting into another Sheet

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:

=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:

Initial setup of the calendar aggregator sheet

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)

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.

 

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.

Calendar Aggregator with Day TrackerTo 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.

Conditional Formatting for Day Tracker on Google Sheets

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!

Share the love


Like the post? Consider donating to fund the maintenance of this website:

  • sponsor a green tea soy latte for a job well done
  • help to maintain this site for another month


Related Posts

Card image cap
Google Sheets Functions to Speed Up Your Work
blog comments powered by Disqus

© 2021 Copyright: pingshiuanchua.com

Terms of Use Privacy Policy