Pulling conference room usage data from Outlook Calendar to input into Excel project

[Shayll]

I've been working on a project for my boss that details room usage at our facility. I have been referencing our conference room Outlook calendar to manually input the number of meetings, start time, end time, and name of the meetings per week. We are working on this project to show upper management how busy/packed our rooms can get on any given week. I was wondering if it was possible to pull all of the data I need from the Outlook calendar and then input that data into Excel without having to do it manually? Here is an email from my boss explaining what she would like done:

"I wish there was an easier way to do this, like if we could export the calendar into excel and make a formula to pull occupied times and compare it to max possible occupancy? Do you think that’s possible? I feel like if we can show facilities that for any given week a room can be occupied for an 8-9 hour work day / 40-45 hours per week and each week it’s occupied 50-80% of that time or whatever then it may save time and get a more quantifiable number"

I'm not great at Excel so I don't know if this is something that is possible but I figured I would ask here and see if anyone has any suggestions! I've attached a picture of the project just for reference.

 

[Taul]

Hi Shayll,
Welcome to the forum.
There are several ways of doing this.

1 The easy way is to change the Outlook calendar to “List View”.
On the ribbon, select "Change View" then select "List View" When in list view, you should be able to select a range of dates then copy and paste to Excel. I don’t think you can select a range of dates if the calendar is in say month or week view. I have an old version of Outlook and I don’t know which version you are using or if the “List View” is available in your version.
I recall this was the case last time I tried it, which admittedly was quite a while ago. Hopefully, it can still be done this way. When you have the data in Excel, then you can set about using formula to display what you want to see.
Be mindful that when you calculate the difference between start and end times, the “All Day Event” is likely to return a value of 24 hrs. So you will need to introduce some nifty checks in the formula.

2 Use the built-in export wizard in Outlook
Export the data to CSV (Comma Separated Values) The CSV format file can be opened directly in Excel. To export follow these steps-

Starting in Outllook click File > Open & Export.
Navigate to Import/Export.
Click Export to a file.
Select Comma Separated Values (CSV).
Click Next and choose the calendar you need to export.
If this method does not work it may be related to your permissions in the calendar.

3 Use an Excel macro to import the data automatically.
An automatic solution would involve VBA. I know I can make it work to pull data form your own calendar or a shared calendar but I have never tried it from a “Room” calendar, so my offering may be a little limited. It may be that a “Room” calendar is no different from any other type of calendar but I’m not sure.
If you would prefer a VBA solution to automatically pull the data into Excel then you will need full permission access to the calendar (Add, edit & delete), I assume you have that already.

Try the first two methods and see if they work for you. Post back if you want to explore the VBA approach.

HTH
Paul.

 

[Taul]

Hi Shayll,
I have just noticed from your sig that you are using office 365.
Office 365 is unknown territory for me and my previous post relates to Win 7
Hopefully someone with 365 knowledge will either confirm what I have posted or offer something else for you.
Paul

 

[Shayll]

Thanks so much for the reply! I was able to get the data in csv format, do you have any suggestions on formulas to use to get the appropriate data I need?

Hi Shayll,
I have just noticed from your sig that you are using office 365.
Office 365 is unknown territory for me and my previous post relates to Win 7
Hopefully someone with 365 knowledge will either confirm what I have posted or offer something else for you.
Paul

 

[Taul]

Hi Shayll

As far as formula are concerned.
Assuming START date/time is in A2 and END date/time is in B2
The in cell C2 :-

 =SUM(B2-A2)

Format the cell C2 (or whichever cell you put the formula in) as “Custom” [h]:mm
Do you have “All Day Events”? or are all the appointments timed?

Paul.

 

[Shayll]

Hi Shayll

As far as formula are concerned.
Assuming START date/time is in A2 and END date/time is in B2
The in cell C2 :-

 =SUM(B2-A2)

Format the cell C2 (or whichever cell you put the formula in) as “Custom” [h]:mm
Do you have “All Day Events”? or are all the appointments timed?

Paul.

All of the events are timed!

 

[Taul]

Hi,
Ok, that makes it easier, you can just use the formula previously posted.

One more thing to consider.
start = 14-02-2020 08:30:00
end = 16-02-2020 15:00:00
The previous formula would return 54:30 hrs whereas you probably want something like (approx) 3 x 7 hrs = 21 hrs in other words only count working hours.

Is it likely the appointments will span more than one day?
If so, do you want to count "working hrs" only?

 

[Shayll]

Hi,
Ok, that makes it easier, you can just use the formula previously posted.

One more thing to consider.
start = 14-02-2020 08:30:00
end = 16-02-2020 15:00:00
The previous formula would return 54:30 hrs whereas you probably want something like (approx) 3 x 7 hrs = 21 hrs in other words only count working hours.

Is it likely the appointments will span more than one day?
If so, do you want to count "working hrs" only?

The meetings don't span multiple days. I do want only the 8hour work day to count. Is it possible to make this formula produce the information my boss asked for in my initial post?

 

[Taul]

What is the first column e.g. "01-010 UG" are these rooms or teams of people?
Could you explain the layout a little better please?

For example: if 01-010 UG is a room (as I assume), and 2nd April 2018 is a Monday, so the blue filled cell is saying the room was fully occupied for that week, is that true, was it fully occupied?
What constitutes being occupied? In other words, how much booked time is considered occupied in order to say lets fill blue into that week cell.
Paul

 

[Shayll]

Hi,
Ok, that makes it easier, you can just use the formula previously posted.

One more thing to consider.
start = 14-02-2020 08:30:00
end = 16-02-2020 15:00:00
The previous formula would return 54:30 hrs whereas you probably want something like (approx) 3 x 7 hrs = 21 hrs in other words only count working hours.

Is it likely the appointments will span more than one day?
If so, do you want to count "working hrs" only?

What is the first column e.g. "01-010 UG" are these rooms or teams of people?
Could you explain the layout a little better please?

For example: if 01-010 UG is a room (as I assume), and 2nd April 2018 is a Monday, so the blue filled cell is saying the room was fully occupied for that week, is that true, was it fully occupied?
What constitutes being occupied? In other words, how much booked time is considered occupied in order to say lets fill blue into that week cell.
Paul

I'm currently working on Conference room 01-115. All of the labels on the left side are rooms. The blue cells indicate that there were at least 4 meetings held in that particular room during that particular week. Hope this clears it up a little!