Time Reporting with Outlook and Powershell

June 11, 2013

Table of Contents

How many people work in companies that require them to keep a Time sheet of all their different projects? You know the answer. Many are forced to have different tools to help with the time tracking and reporting.outlook calenders are used by many people for this task as they are made for many devices, still there is no reporting that these devices are capable of doing.

it even gets worse when you are working as a group and you want to find out what time they are spending on tasks like “host maintenance” , getting all this information will be very tedious to perform, this powershell solution will help people to figure oput how much time a group of people were spendingon “sales engineering” and a few modifications to allow different users and categories.

It has features including:

Category

Use –category to add on timethough it only supports single category.

Multiple calendars

Use –calenders  to enter a single name like “john clark” or multip[le in an array:@(“john clark”,or martin green”).

Data range

Without a few changes by default the current week M-F will be reported. Those who always something unique from others you can use –pickdates to have a graphicalcalendar to each start and end date( just click the date then press enter ) or use –rangeStart and –rangeEnd  followed by “7/13/2014”

HTML or CSV output

Outputs are to by default to html report but you can use -csv  for outputting comma separated value file.

Below is the script to do all these:

#

# OutlookTimeReport.ps1

# john clark

# 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] >= ‘{0}’ AND [Start] <= ‘{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 “$HOMEdesktopOutlookTimeReport.csv”

 Invoke-Item “$HOMEdesktopOutlookTimeReport.csv”

}

else

{

$seHTML = $seArray | ConvertTo-Html -Body “</pre>

<h3>$($category): $($rangeStart.ToString(“MM.dd.yyyy”)) – $($rangeEnd.ToString(“MM.dd.yyyy”))</h3>

<pre>”| Out-File “$HOMEdesktopOutlookTimeReport.html”

 Invoke-Item “$HOMEdesktopOutlookTimeReport.html”

}

Home

Share on
Facebook
Twitter
LinkedIn
Pinterest
More posts

Dedicated Servers Quick Guide

What is a Dedicated Servers? Why bother using a dedicated server over a VPS or Shared Hosting?A dedicated server is a server 100% dedicated to your website/project or business needs.

VMware vCenter Server 5.1 Update 2

There are some new features in the new VMware released some days back a vCenter Server 5.1 update 2 build 1473063. This update enables the customization for windows 8.1, windows

Get 90% Discount

First 3 People gets the Bonus!
Don't Miss Out Our Big Sale

Get 0-90% On All
247Rack Services

247Rack

The Sale Is Until The End Of March