You mean I'm only spending two hours a week on Project X?

August 22, 2012 6:01 AM

Is it possible to extract all meeting data from Outlook to quantify time use?

I'm working on quantifying time use and my dataset is based on an Outlook calendar populated with meetings, etc.

Is there an easy way to extract all meetings from Outlook in one go (e.g. pull types of meetings based on type into an Excel workbook)? The end goal is to see how much time per week/month is being spent on each item, and then figuring out how best to shift and re-prioritize based on what I learn.

Right now, the data-pulling is tedious (i.e. I am going through Outlook week by week and manually entering piecemeal meetings into Excel), and am hopeful about the possibility of some trick to pull data from Outlook without all of the "touch". Does this exist?

Thanks so much for any help, MeFi!

posted by simulacra to computers & internet (8 answers total)

Powershell! Run this script to pull outlook calendar data into powershell objects. You'll want to do something like:

Get-OutlookCalendar |
where-object { $_.start -gt [datetime]"5/10/2011" -AND $_.start -lt `
[datetime]"5/17/2011" } | select-object List, of, properties, for, the, meetings, you, want, exported | Export-csv -Path "c:\meetings.csv"

Yeah, you can use powershell to export to excel, but this is a one-off, so I'd just go to csv.
posted by bfranklin at 6:13 AM on August 22, 2012 [1 favorite]

This looks promising. But, I have no idea how to use code. Help? How / where do I run this script?

Thanks much!
posted by simulacra at 2:50 PM on August 22, 2012

If you're running XP, google "Install Powershell". If you're running 7, start->all programs->accessories->powershell->Powershell ISE. Input the script then press the play button in the menu bar.

Function Get-OutlookCalendar
{
<> .Synopsis
This function returns appointment items from default Outlook profile
.Description
This function returns appointment items from default Outlook profile. It
uses the Outlook interop assembly to use the olFolderCalendar enumeration.
It creates a custom object consisting of Subject, Start, Duration, Location
for each appointment item.
.Example
Get-OutlookCalendar |
where-object { $_.start -gt [datetime]"5/10/2011" -AND $_.start -lt `
[datetime]"5/17/2011" } | sort-object Duration
Displays subject, start, duration and location for all appointments that
occur between 5/10/11 and 5/17/11 and sorts by duration of the appointment.
The sort is shortest appointment on top.
.Notes
NAME: Get-OutlookCalendar
AUTHOR: ed wilson, msft
LASTEDIT: 05/10/2011 08:36:42
KEYWORDS: Microsoft Outlook, Office
HSG: HSG-05-24-2011
.Link
Http://www.ScriptingGuys.com/blog
#Requires -Version 2.0
#>
Add-type -assembly "Microsoft.Office.Interop.Outlook" | out-null
$olFolders = "Microsoft.Office.Interop.Outlook.OlDefaultFolders" -as [type]
$outlook = new-object -comobject outlook.application
$namespace = $outlook.GetNameSpace("MAPI")
$folder = $namespace.getDefaultFolder($olFolders::olFolderCalendar)
$folder.items |
Select-Object -Property Subject, Start, Duration, Location
} #end function Get-OutlookCalendar

Get-OutlookCalendar | where-object { $_.start -gt [datetime]"5/10/2011" -AND $_.start -lt [datetime]"5/17/2011" }



When you run that you're going to get a mess of objects outputed in the bottom pane. Look through the properties of them, pick the properties you want, then replace the last line of the above script with the one I initially gave you.

If you need to do manipulations in office like this on anything resembling a regular basis, powershell is ridiculously simple to learn and makes life much, much better. Things like converting excel spreadsheets of vendor answers to 75 questions into a standard risk analysis report now takes about 20 minutes rather than 90.
posted by bfranklin at 5:03 PM on August 22, 2012

And mefi just did some fu on my last post. The first three lines of the script are

Function Get-OutlookCalendar
{
<# .Synopsis
posted by bfranklin at 5:05 PM on August 22, 2012

I don't have Outlook in front of me, and I can post more tomorrow. But if you change the view to "By Category", then "Customize Current View", you can apply a filter. The filtered list can be selected (ctrl-a), copied, and pasted into a spreadsheet. You'll need to make sure you get the fields you need when you copy the filtered list.

As I said, I'll get you the exact steps tomorrow.
posted by Gorgik at 9:05 PM on August 22, 2012

Ok, here you go:

View>Current View>By Category

You can just select all>copy>paste into Excel from there.

Or, if you want to filter the list,

View>Current View>Customize Current View, and select "Filter". You can set a filter, then select the results and paste into Excel.
posted by Gorgik at 1:35 PM on August 23, 2012

Yeah, or you can export to CSV and manipulate in Excel.
(Import/Export --> to a file --> select your calendar --> specify date range)
posted by misterbrandt at 8:18 AM on August 24, 2012

Long delay!

Does anyone know how to pull data from a shared calendar? That's been my dilemma. The previous scripts were unsuccessful, if only because I'm pulling from my own calendar, so had to enter each event/meeting piecemeal into Excel--defeating the purpose of a script.

Thanks so much.
posted by simulacra at 11:05 AM on September 18, 2012