Time Reporting with Outlook and Powershell

Table of Contents

Facebook
Twitter
LinkedIn
Pinterest

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

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.

30 VMware vSphere Performance Tips

You most likely chose VMware’s vSphere as your virtualization solution due to it’s reputation as a solid and performant product; however, without proper attention and optimization you will not fully utilize the

Dedicated Server or VPS Server?

What is the difference between Dedicated Server or VPS Server?In short, VPS hosting works on a virtual shared server, where you host your website alongside others; dedicated hosting, meanwhile, gives