How to Build a JavaScript Global Meeting Planner Application

Today, an increasing number of businesses operate with teams that span multiple time zones. The teams can span across many countries, and the companies' communication capabilities have made this possible. The issues arise, of course, not from the tools we use to conduct the meetings themselves but from finding the time to do so. How do you plan a meeting that spans multiple time zones?

Here we will briefly explain how you can use various SpreadJS features to create a world meeting planner fully customizable for your application needs.

Note: download the sample file here, and follow along.

When your team members are located worldwide, planning conference calls and meetings becomes a challenge because you need to cater to multiple time zones and daylight savings rules. 

This blog will show how to create a Global Meeting Planner that allows users to find the best appointment times for cross-time zone meetings using SpreadJS, our JavaScript spreadsheet component. The spreadsheet would have two pages.

First, we would have to create a page where all the DST Rules would be placed, and second create the main sheet where the user would have to list up the members/locations, their UTC Offsets, and choose the appropriate DST Rule for each one. After that setup, the user would have to enter the meeting time and check the Meeting Planner Table to see whether it will work.

DST Rules

The complexity of this sample comes from the Daylight Saving Time (DST) rules, which are different throughout the world. DST, or Daylight Saving Time, is a time of year when clocks are advanced by one hour. This has the effect of increasing the number of sunlight hours in the evening during the summer months.

We move our clocks forward one hour at the start of DST and back one hour when we return to standard time (ST). The change from standard time to daylight saving time shifts one hour of daylight from the morning to the evening. The change from Daylight Savings Time to Standard Time effectively changes one hour of daylight from evening to morning.

We have put in a new sheet all these DTS rules. The user can add or remove rules as well as modify those. 

Each rule defines the dates that DST begins and ends, and if a location is ON Datetime Saving Rule. 1 hour is added to the UTC offset if the condition is met.

The user can change the Year (at the top of the sheet), and the Start and End Date of the DST will change accordingly in an automatic way.

Settings

For our sample, some calculations must be made. In the following picture, you can see all the variables that the user needs to enter.

• Enter the Meeting Date, Meeting Time

You must also enter the DST Rules and UTC Offsets based on YOUR location.

• Enter Labels for the Locations and Team Members
• Enter the Standard UTC Offset and DST Rules for the Locations mentioned above.
• Enter team member Availability and Flexibility.

We will be using formulas to make some calculations based on user inputs.

Firstly, the End Date and Time of the meeting will be calculated based on the start date/time and duration.

Notice the highlighted rows in the picture above. We will be doing some helping calculations on these rows. The breakdown of those is as below:

Row 7-8:

For dstBegin (cell $C$7) use this formula: =INDEX('DST Rules'!$B$6:$B$17,MATCH(dstRule,'DST Rules'!$D$6:$D$17,0))

For dstEnd (cell $C$8) use this formula: =INDEX('DST Rules'!$C$6:$C$17,MATCH(dstRule,'DST Rules'!$D$6:$D$17,0))

Next, we calculate the start GMT and end GMT based on the user entered start date/time, UTC Offset, and DST rules.

For startGMT (cell $G$8) use this formula: =startDT-(utcOffset+IF(dstBegin="n/a",FALSE,IF(dstBegin>dstEnd,OR(startDT>=dstBegin,startDT<=dstEnd),AND(startDT>=dstBegin,startDT<=dstEnd))))/24

For endGMT (cell $I$8) use this formula: =endDT-(utcOffset+IF(dstBegin="n/a",FALSE,IF(dstBegin>dstEnd,OR(endDT>=dstBegin,endDT<=dstEnd),AND(endDT>=dstBegin,endDT<=dstEnd))))/24

Row 15-17:

The formulas below are used to calculate the DST Begin, DST End, and current Offset for each of the members based on the locations entered:

DST Begin (C$15):  =INDEX('DST Rules'!$B$6:$B$17,MATCH(C13,'DST Rules'!$D$6:$D$17,0))

DST End (C$16):  =INDEX('DST Rules'!$C$6:$C$17,MATCH(C13,'DST Rules'!$D$6:$D$17,0))

DST Offset (C$17):  =1*IF(C15="n/a",FALSE,IF(C15>C16,OR(startDT>=C15,startDT<=C16),AND(startDT>=C15,startDT<=C16))) - if the meeting date is during Daylight Saving Time the offset would be 1, if not it would be 0. For the locations with no DST Rules offset is always 0.

Drag each of the formulas to the right to calculate the variables for all the locations.

Row: 28-29:

gmt (cell $C$28) use this formula: =startDate-(utcOffset+IF(dstEnd="n/a",FALSE,IF(dstEnd>dstEnd,OR(startDate>=dstEnd,startDate<=dstEnd),AND(startDate>=dstEnd,startDate<=dstEnd))))/24

For cell $C$29 (Meeting Planner Table starting time) use this formula*=gmt+($C12+$C17)/24.* You can drag this formula to calculate the starting time of the various locations (in our example, drag it to $K$29, if there are more or fewer locations/members do the necessary corrections).

Note: For easier visualization in the formulas, we used range names instead of giving cell references.

Before we jump to the meeting table - a table meant to make it easy to distinguish between available hours for different locations and hours where the team can be flexible to participate, hide the rows as shown on the picture, so you have shown only the needed information and not the calculations.

Meeting Planner Table

The Meeting Table has two major sections. On the left, there is the list of the UTC Times, starting with the value of GMT stated in the paragraph above and adding 1 hour for the successive rows. 

Below the Locations, we will reference the values calculated on the 29th row (=gmt+(C12+C17)/24) and add 1/24 or 1 hour for all the successive rows.

We will use Custom Format to format the column containing the UTC Time (use [$-409]\ddd,\  dd\  mmm,\ hh:mm as data format) and the columns containing the times for every location entered (use [$-409]ddd\ hh:mm;@ as data format and set background as RED).

Meanwhile, we will be using Conditional Formatting to color the location hours regarding the availability of each user or the flexible hours of each.

Follow the steps below:

1. Select the cells.
2.  In the Home tab, select the Conditional Formatting menu.
3. Click New Rule.
4. Select "Use a formula to determine which cells to format" as a rule type.
5. Set the formula and then set the format by clicking the Format button.

The table below shows the conditional rules together with their formulas and cell selections used in this sample:

Below is the view of the Conditional Formatting Rules Manager:

By default, the color of the hours will be RED, meaning the user is not available.

Final Result

The picture below shows the result. You must enter the information only on the needed cells as the others are calculated automatically. For better visualization, all the other cells containing calculated information are hidden.

How to use this World Meeting Planner

1. Enter your location and the locations of the participants for your meeting or conference. If DST rules are applied, enter those as well.  
2. Your World Meeting Planner results will have color-coding to make it easy to see at a glance when the best times to meet would be for all of the participants.   If you see GREEN or YELLOW times for all your locations, you have found an excellent time to meet as everyone would be available or would have flexibility. On the other hand, any time with a RED color would not be a good time for your conference call or meeting.  
3. Once you have determined a good time to have your conference call, the results page provides you with the local time for each location, and this way, you have the needed info to send to the participants.

Conclusions

Need to make a call to someone far away or arrange a web or video conference across different time zones? Find the best time across time zones using our JavaScript spreadsheet components.

Use SpreadJS to add spreadsheet capabilities to your JavaScript applications spreadsheet. With various features and functionalities like conditional/custom formatting and a powerful engine with 500 built-in functions, custom functions, array functions, dynamic arrays, or formula textbox, you can have at your fingertips the power to embed in your enterprise application true Excel-like spreadsheet experiences.