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”
}
