• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Utilisation chart based on Excel data exported out of Outlook Calendar [SOLVED]

K

KFozzy

New Member
Dear All

I thought I ask around before I start building my own:


Have anyone ever done some kind of facility/resource utilization report based on the standard Excel data you get when you do an export from an Outlook Calendar?


I'm supervising 10 meeting rooms - they all have their automated calendars from which I export all the data out into Excel.

I'd like to know/visualise how and when the 10 meetingrooms are being used - time of day, frequency, and the like.


Any ideas - sample spreadsheets - I can try?


Thank you,

Krisztina
 
Hi, KFozzy!

Never used Outlook calendar, I know that my assistants and colleagues should have used them before but I always refused to join them in that practice.

If you could post a sample file with the exported data I'd give a look to it.

Regards!

PS: BTW, thank you, you made me remembering about checking what email accounts I actually have in the Outlooks of my computers (except in the new one, the others might yet have non-existing accounts. :$
 
Thank you Sir J.


Here is a link to a sample sheet, showing my meeting room 1 for May 2013:


https://dl.dropboxusercontent.com/u/87324996/Calendar%20export.xls


This is how it comes out of Outlook export - no modifications, apart from anonymisation.


Any insights would be appreciated.


K.
 
Hi Krisztina ,


One question - suppose all the calendars are consolidated , where does the meeting room number appear ?


The text IJR Suite appears in one place ; is this it ?


Ideally , the Location column should be filled in.


Narayan
 
I'm sorry for the late response - I was "offline" all weekend.


So, the meeting room number doesn't appear on the export, but I could add that field for each of the 10 room's export (into the location field, if necessary). IRJ Suite is the overall name of the meeting suite and also the "user name" for this public calendar I've got the export from.


My example file only shows Meeting Room 1.
 
Hi, KFozzy!


Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/Utilisation%20chart%20based%20on%20Excel%20data%20exported%20out%20of%20Outlook%20Calendar%20-%20Calendar%20export-2%20%28for%20KFozzy%20at%20chandoo.org%29.xlsx


It uses a helper worksheet (CalendarWork) to properly convert to valid date & time values the exported data (Calendar), and builds a monthly occupancy list for each of ten rooms assuming they're numeric from 1 to 10 (RoomOcupation).


The minimum time lapse is set to 15' accordingly to the values in worksheet Calendar. In cell A1 of worksheet RoomOccupation you should enter the date of the 1st day of the related month.


This solution uses 4 dynamic named ranges on helper worksheet: CalendarTable, RoomList, StartList and EndList, and assumes that your originally missing room number is in column A. Adjust them properly if this weren't the case.


Just advise if any issue.


Regards!
 
Thank you Sir JB - this looks like just the ticket. Your time and effort are much appreciated!


I cannot wait to export out all the ten rooms, plug them in accordingly and show the details in some funky chart for all to see! That's going to be a great new addition to my monthly FM report!


Regards,

K.
 
One more question:


I exported and plugged in 9 rooms so far (the 10th is not in use currently).

All the dynamic ranges are picking up all the records, but the RoomOccupation table doesn't update. I tried to look into why, but no luck... would you mind having a view?


https://dl.dropboxusercontent.com/u/87324996/Meeting%20room%20utilisation%20report_May%202013.xlsx


Thank you,

K.
 
Hi SirJB


I'm sorry. Try this...


https://dl.dropboxusercontent.com/u/87324996/Meeting%20room%20utilisation%20report_May%202013.xlsx


Or shortened:

http://db.tt/95FHic25


And thank you again for helping!


K.
 
Hi, KFozzy!


Sorry, my mistake, missing fixed referencing for A column.


Please download again the fixed originally uploaded file:

https://dl.dropboxusercontent.com/u/60558749/Utilisation%20chart%20based%20on%20Excel%20data%20exported%20out%20of%20Outlook%20Calendar%20-%20Calendar%20export-2%20%28for%20KFozzy%20at%20chandoo.org%29.xlsx

and your fixed last uploaded one:

https://dl.dropboxusercontent.com/u/60558749/Utilisation%20chart%20based%20on%20Excel%20data%20exported%20out%20of%20Outlook%20Calendar%20-%20Meeting%20room%20utilisation%20report_May%202013%20%28for%20KFozzy%20at%20chandoo.org%29.xlsx


Just advise if any issue.


Regards!
 
My gratitude!


I was trying to play around with the relative vs. absolute referencing in column A, as I suspected that to be the issue, but couldn't find my way totally around it.


Thank you again,

K.
 
Hi, KFozzy!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!

PS: I apologize for the referencing error, it won't happen again...

PS2: ... until next time :P
 
In case you need something a bit more polished or in a more graphical format: there is an Excel Addin App Called WinCalendar that converts Outlook Calendar to Excel format in a variety of layouts. If can do similar to the excel file attached above, but in a timeline (Gantt) layout.
 
You must log in or register to reply here.
Back
Top