In some businesses it is very important to know who booked a resource for how many hours. Co-working spaces are charging for meeting rooms on an hourly basis. They need to prepare utilization reports to bill their customers. Other businesses might be interested to know more about weekly booking behavior of staff or which department is using the resource most intensively.

This blog post shows a way how this could be done by using identifiers or code names in the subject line. It’s possbile to use the organizer or other data fields. In our example, we’ll focus on bookings that are made with Meeting Room Schedule door displays. In most cases there will be a mix of meetings created regularly using Outlook, Gmail and using the booking feature of door displays. The method works for all type of Google/Gsuite calendar entries. The method can be adapted to MS Exchange and Microsoft 365 (formerly known as 0365) environments.

1. Create Meeting Room Bookings from Tablet Entering Subject by “Company/Person”

The String entered can be a nickname or PIN as well. The regular subject can follow the identifier if needed. In the example “Abc”, “And” and “123” are companies. After the slash character, it is agreed upon to use the first name of the booking person.

The book room button brings up a pop-up asking for a subject. At this point users need to stick to the naming guidelines. Otherwise there will be more work in the data cleaning step. The same is true for typos. PINs that are validated by the software could be more safe but that requires users to handle another PIN or password. Giving dropdown options from a list of shortened “cost center” names is an option, too.

 

2. Log-in and Check the Calendar view in Gsuite

The screenshot shows the same tablet booking in the browser view of Google calendar.

 

3. Calendar Export of the Resouce Calendar in Gsuite

Needs to be done by the Admin or other users who have access to the resource calendar. Access calendar settings by pressing the three dots in the calendar list. Pressing “Export Calendar” is going to create a zipped ics-file.

Extract the zip archive afterwards. You’ll get an ics file. Microsoft Excel won’t load ics files directly into a nice formatted table. There are multiple ways of importing ics into Excel. Skilled Excel users can handle this task without using any tools. There are multiple free tools available. Just google it.
 

4. Convert ics to csv using a Free Online Tool

This is the tool we have chosen in our example.

https://www.indigoblue.eu/ics2csv/

This can be done with the built-in Microsoft Excel import features without using this tool. But it’s going to take more time for the average Excel user.

 

5. Choose Settings for ics2csv Conversion

The online tool asks for your ics file and gives some basic options for your import. Use comma as a seperator, check your time format and keep time in events.

 

6. View Raw Results in Excel

In this step you need to reduce the imported calendar entries to your reporting period. Check summary and dates exported from the meeting room bookings.

 

7. Clear data and add two new Columns to Calculate Duration of Bookings

Delete bookings that are not relevant and edit those which are ambigous. In our example, the subtraction of end time from start time gives a fraction of a day. This intermediate result can be multiplied by 24 to get the duration in hours. Check Formula in screenshot.

 

8. Finalize Sums for Reporting or Billing

Now it’s time to create the final sums according your reporting requirements. In our example the companies “Abc” and “And” are getting hours listed for each user. For bookings from company “123” we use SUMIF to build a sum for all users. This way you can deliver different types of reports according to your requirements. For example, in some countries, reports that could be used to track an individual’s activities must not include personally identifiable information and need to be anonymized.