GeekAfterFive - Infrastructure as Code

home

Time Reporting with Outlook and Powershell

18 May 2012

Do you work at a company that requires you to report your time? Are you curious how much time you are spending on different projects or duties? Hate having a separate tool just for time tracking and reporting? (me too!) My Outlook calendar makes for easy time tracking. It's on my iPhone, my desktop, my laptop, my home PC...but there are no reporting capabilities that I am aware of, and I am not going to buy an app or plugin that I have to manage. What if I wanted to find out how much time the rest of my team is spending on something like "host maintenance?" They have their calendar shared with me, and I can see their categories, but I would still have to manually add up their time, force them to do double data entry to another app, or something else that requires double-data-entry. I wrote this Powershell solution to figure out how much time a group of people were spending on "Sales Engineering," and made a couple modifications for this post to allow different users and categories.

Features:

Multiple calendars

use -calendars to enter a single name like "Jake Robinson" or multiple in an array: @("Jake Robinson", "Alan Renouf")

Date Range

By default the current week M-F will be reported. If you need something a little different, you can use -pickDates to bring up a graphical calendar to each start and end date (click the date then press enter). You can also use -rangeStart and -rangeEnd with something like this "5/24/2012"

HTML or CSV output

Outputs to HTML report by default, or use -csv to output to comma separated value file.

Screenshots!

My Calendar

...and here's the script!

#
# OutlookTimeReport.ps1
# Jake Robinson
# v1.2
#
#
#
#

param ( [DateTime] $rangeStart = (get-date -hour 0 -minute 0 -second 0).AddDays(-(get-date).DayOfWeek.value__),
 [DateTime] $rangeEnd = (get-date -hour 23 -minute 59 -second 59).AddDays(7-(get-date).DayOfWeek.value__),
 [String] $category,
 [String] $calendars,
 [switch] $csv,
 [switch] $pickdates)

function pick-date()
{
 $objForm = New-Object Windows.Forms.Form

$objForm.Text = "Select a Date"
 $objForm.Size = New-Object Drawing.Size @(190,190)
 $objForm.StartPosition = "CenterScreen"

$objForm.KeyPreview = $True

$objForm.Add_KeyDown({
 if ($_.KeyCode -eq "Enter")
 {
 $dtmDate=$objCalendar.SelectionStart
 $objForm.Close()
 }
 })

$objForm.Add_KeyDown({
 if ($_.KeyCode -eq "Escape")
 {
 $objForm.Close()
 }
 })

$objCalendar = New-Object System.Windows.Forms.MonthCalendar
 $objCalendar.ShowTodayCircle = $False
 $objCalendar.MaxSelectionCount = 1
 $objForm.Controls.Add($objCalendar)

$objForm.Topmost = $True

$objForm.Add_Shown({$objForm.Activate()})
 [void] $objForm.ShowDialog()

if ($dtmDate)
 {
 return $dtmDate
 }
}

if ($pickdates)
{
 [void] [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")
 [void] [System.Reflection.Assembly]::LoadWithPartialName("System.Drawing")

 [DateTime] $pickedStart = pick-date
 [DateTime] $pickedEnd = pick-date

 If ($pickedStart -and $pickedEnd)
 {
 $rangeStart = $pickedStart
 $rangeEnd = $pickedEnd
 }
}

Add-Type -AssemblyName Microsoft.Office.Interop.Outlook

$class = @”
using Microsoft.Office.Interop.Outlook;public class MyOL
{
 public MAPIFolder GetCalendar(string userName)
 {
 Application oOutlook = new Application();
 NameSpace oNs = oOutlook.GetNamespace("MAPI");
 Recipient oRep = oNs.CreateRecipient(userName);
 MAPIFolder calendar = oNs.GetSharedDefaultFolder(oRep, OlDefaultFolders.olFolderCalendar);
 return calendar;
 }
}
“@

Add-Type $class -ReferencedAssemblies Microsoft.Office.Interop.Outlook

$outlook = new-object MyOL
$restriction = "[End] &gt;= '{0}' AND [Start] &lt;= '{1}'" -f $rangeStart.ToString("g"), $rangeEnd.ToString("g")
$seArray = @()
foreach($se in $calendars)
{
 $seObject = New-Object PSObject
 $SECalendar = $outlook.GetCalendar($se)
 $appointments = $SECalendar.items
 $appointments.Sort("[Start]")
 $appointments.IncludeRecurrences = $true
 $SEappts = $appointments.Restrict($restriction) | where {$_.categories -match $category}
 $SEhours = ($SEappts | Measure-Object -Sum -Property duration).sum / 60
 Add-Member -MemberType noteproperty -Value $se -Name "Team Member" -InputObject $seObject
 Add-Member -MemberType noteproperty -Value $SEhours -Name "Hours" -InputObject $seObject
 $seArray += $seObject
}
$totalHours = ($seArray | measure-object -Sum -Property hours).sum
$totalsObject = New-Object PSObject
Add-Member -MemberType noteproperty -Value "TOTAL HOURS" -Name "Team Member" -InputObject $totalsObject
Add-Member -MemberType noteproperty -Value $totalHours -Name "Hours" -InputObject $totalsObject
$seArray += $totalsObject

if ($csv)
{
 $seArray | Export-Csv -NoTypeInformation -Path "$HOME\desktop\OutlookTimeReport.csv"
 Invoke-Item "$HOME\desktop\OutlookTimeReport.csv"
}
else
{
$seHTML = $seArray | ConvertTo-Html -Body "&lt;/pre&gt;
&lt;h3&gt;$($category): $($rangeStart.ToString("MM.dd.yyyy")) - $($rangeEnd.ToString("MM.dd.yyyy"))&lt;/h3&gt;
&lt;pre&gt;"| Out-File "$HOME\desktop\OutlookTimeReport.html"
 Invoke-Item "$HOME\desktop\OutlookTimeReport.html"
}

For more Powershell and Outlook fun, see Alan's post!