Creating a Simple Time-Reporting Tool Based on the Outlook 2010 Calendar

This Visual How To describes a time-reporting add-in for Microsoft Outlook 2010 that uses the categories assigned to calendar items to summarize time that is spent in an arbitrary period. The add-in functions well for time tracking, with several important caveats (which are described in the Read It section). This Visual How To describes what the tool does and explains the add-in code, with a focus on filtering appointment items and accessing the appointment categories and associated appointment properties.

This code sample that accompanies this Visual How To uses a C# Outlook add-in project written in Visual Studio 2010 and assumes that you are already familiar with C# and creating custom forms and add-ins for Outlook.

The add-in has several classes of interest:

• The RequestSummaryForm class, which is a Windows Form.

• The ScheduleReportGenerator class, which contains the reporting logic.

• The ScheduleItem and ScheduleReport classes, which are defined inside the ScheduleReportGenerator class. These two classes encapsulate some of the reporting data and logic.

RequestSummaryForm Windows Form

The following code creates and displays the Windows Form, RequestSummaryForm, to collect the user input. When the form loads, it initializes the from and to DateTimePicker controls to select the current day. It also gets the available categories for the current Outlook session and initializes the category CheckedListBox control.

The event handler for the Generate Report button performs some validation, prompts the user for the output file, and then generates the report. The beginning time is forced to 12:00 A.M. on the from date, and the ending time is forced to 12:00 A.M. on the day following the to date.

/// <summary>A form for requesting a new schedule report.</summary>
/// <remarks>Collects the from and to dates, the categories on which to 
/// report, whether to include daily subtotals in the report, and the 
/// path of the file to which to save the report.</remarks>
public partial class RequestSummaryForm : Form
{
    //...

    private void RequestSummaryForm_Load(object sender, EventArgs e)
    {
        // Initialize the from- and to- DateTimePicker initial dates.
        fromDateTimePicker.Value = toDateTimePicker.Value = DateTime.Now;

        // Initialize the category selection CheckedListBox.
        categoriesCheckedListBox.SelectionMode = SelectionMode.One;
        categoriesCheckedListBox.Items.AddRange(
            GetCategoryList().ToArray());
        prevCategoryIndex = categoriesCheckedListBox.SelectedIndex;

        ignoreDateChangeEvents = false;
    }

    /// <summary>Gets a list of category names for the current Outlook
    /// session.</summary>
    private List<string> GetCategoryList()
    {
        List<string> categories = new List<string>();
        Outlook.NameSpace session = Globals.ThisAddIn.Application.Session;
        foreach (Outlook.Category category in session.Categories)
        {
            string trimmed = category.Name.Trim();
            if (!categories.Contains(trimmed))
            {
                categories.Add(trimmed);
            }
        }
        categories.Sort();
        return categories;
    }

    //...

    private void generateReportButton_Click(object sender, EventArgs e)
    {
        if (fromDateTimePicker.Value.Date > toDateTimePicker.Value.Date)
        {
            MessageBox.Show("The to date is earlier than the from date " +
                "for the report. No report generated.");
            return;
        }
        if (categoriesCheckedListBox.CheckedItems.Count == 0)
        {
            MessageBox.Show(
                "No categories selected. No report generated.");
            return;
        }

        // Get the path for the output file to create.
        DialogResult result = this.saveFileDialog1.ShowDialog();

        if (result == DialogResult.OK)
        {
            Cursor = Cursors.WaitCursor;

            // Change the reporting period to be from 12 A.M. on the start 
            // date to 12 A.M. on the day after the end date.
            DateTime fromDate = fromDateTimePicker.Value.Date;
            DateTime toDate = toDateTimePicker.Value.Date.AddDays(1);

            // Create and save the time tracking report.
            ScheduleReportGenerator.GenerateReport(
                fromDate, toDate, Categories,
                dailySubtotalsCheckBox.Checked, saveFileDialog1.FileName);

            Cursor = DefaultCursor;

            result = MessageBox.Show("Report was generated successfully.");

            this.Close();
        }
    }

    //...

    /// <summary>Gets the categories on which to report.</summary>
    /// <remarks>Returns the categories that are checked in the categories
    /// CheckedListBox.</remarks>
    private HashSet<string> Categories
    {
        get
        {
            HashSet<string> categories = new HashSet<string>();
            foreach (string category in
                categoriesCheckedListBox.CheckedItems)
            {
                categories.Add(category);
            }
            return categories;
        }
    }
}

ScheduleReportGenerator Class

In addition to defining the ScheduleItem and ScheduleReport inner classes, the ScheduleReportGenerator class defines methods for retrieving the categories that are assigned to an Outlook appointment item and for getting the Outlook appointments that occur within the reporting period.

The Outlook AppointmentItem interface has a Categories property that returns a string that contains the category names assigned to the appointment. The categories string uses the list separator character for the current UI culture. The Split() method, which this example uses to turn the string into an array, takes an array of separator characters or strings. Therefore, the following code stores the list separator in a string array. The GetAppoinmentCategories method then uses the ListSeparator property to get the set of categories that are assigned to an Outlook AppointmentItem.

/// <summary>The separator character used in the category list.</summary>
private static readonly string[] listSeparator =
    new string[] { CultureInfo.CurrentCulture.TextInfo.ListSeparator };

/// <summary>Gets the set of categories assigned to an Outlook calendar
/// item.</summary>
/// <param name="appointment">The item from which to get the categories.
/// </param>
/// <returns>The categories assigned to the item.</returns>
private static HashSet<string> GetAppointmentCategories(
    Outlook.AppointmentItem appointment)
{
    if (string.IsNullOrEmpty(appointment.Categories))
    {
        return new HashSet<string>();
    }

    string[] rawCategories = appointment.Categories.Split(
        listSeparator, StringSplitOptions.RemoveEmptyEntries);

    HashSet<string> categories = new HashSet<string>();
    foreach (string rawCategory in rawCategories)
    {
        string category = rawCategory.Trim();
        if (category.Length > 0)
        {
            categories.Add(category);
        }
    }
    return categories;
}

The GetCalendarItemsInTimeFrame method, shown in the following code, gets the default calendar for the current Outlook session and uses a filter to get a subset of the calendar items in the reporting period.

To iterate recurring appointments in a Calendar folder, first obtain the Items collection for the folder, sort the Items collection by the Start property, and then set the IncludeRecurrences property for the Items collection to true. Then, apply the filter to the updated collection to get the relevant appointments.

To include appointments that overlap or span the reporting period, the filter includes any unique or recurring appointment that starts before the end of the report and that ends after the beginning of the report. For more information about filtering appointments, see How to: Search and Obtain Appointments in a Time Range.

public static Outlook.Items GetCalendarItemsInTimeFrame(
    DateTime reportStart, DateTime reportEnd)
{
    Outlook.NameSpace session = Globals.ThisAddIn.Application.Session;
    Outlook.Folder calendar = session.GetDefaultFolder(
            Outlook.OlDefaultFolders.olFolderCalendar)
            as Outlook.Folder;

    // Specify the filter this way to include appointments that
    // overlap with the specified date range but do not necessarily
    // fall entirely within the date range.
    // Date values in the filter must not include seconds.
    string filter = string.Format(
        "[Start] < '{0}' AND [End] > '{1}'",
        reportEnd.ToString("MM/dd/yyyy hh:mm tt"),
        reportStart.ToString("MM/dd/yyyy hh:mm tt"));

    // Include recurring calendar items.
    Outlook.Items calendarItems = calendar.Items;
    calendarItems.Sort("[Start]", Type.Missing);
    calendarItems.IncludeRecurrences = true;
    calendarItems = calendarItems.Restrict(filter);

    return calendarItems;
}

Because the code sample uses the comma-separated value (CSV) format to save the report data, and the appointment subject and category names can contain commas and quotation marks, which are special characters in CSV format, the following code defines the CsvEscape method for correctly escaping a string.

/// <summary>Returns an escaped string that represents the original
/// value in CSV format.</summary>
/// <param name="s">The string value to encode.</param>
/// <remarks>Adds leading and trailing quotation marks and escapes
/// internal quotation marks by replacing them with two consecutive
/// quotation marks. This is necessary when a field contains any
/// quotation marks or commas./// </remarks>
private static string CsvEscape(string s)
{
    return string.Format("\"{0}\"", s.Replace("\"", "\"\""));
}

ScheduleItem Inner Class

The ScheduleItem class, shown in the following code, collects information about an appointment. This class extrapolates information from the AppointmentItem, such as the following:

• The subset of categories, exposed through the RelevantCategories property, that is assigned to the appointment that the user selected to appear in the report.

• The number of hours, exposed through the HoursWithinReportingPeriod property, for the part of the appointment that occurs within the reporting period.

  Note

  To calculate the number of hours to report per category, the add-in divides HoursWithinReportingPeriod by the count of categories in RelevantCategories. That way, the total hours for each category reported for the appointment equals the total time reported for the appointment. Your business logic may be different, so this is an area to consider revising before you use or adapt this tool.

/// <summary>Represents an Outlook calendar item or appointment in
/// the context of the report to generate.</summary>
internal class ScheduleItem
{
    private ScheduleReport report;
    private string subject;
    private DateTime start;
    private DateTime end;
    private HashSet<string> relevantCategories;
    private double duration;

    public ScheduleItem(
        ScheduleReport report, Outlook.AppointmentItem appointment)
    {
        this.report = report;
        this.subject = appointment.Subject;
        this.start = appointment.Start;
        this.end = appointment.End;
        this.relevantCategories = GetAppointmentCategories(appointment);
        this.relevantCategories.IntersectWith(
            report.ReportingCategories);
        this.duration = appointment.Duration / 60.0;
    }

    /// <summary>Returns the appointment information in CSV format.
    /// </summary>
    /// <remarks>Outputs subject, hours per category (multiple),
    /// (empty), reported hours, (empty), start date, start time,
    /// end date, end time, appointment duration.</remarks>
    public string ToCsvString()
    {
        StringBuilder sb = new StringBuilder();
        sb.Append(CsvEscape(subject));
        foreach (string category in report.ReportingCategories)
        {
            double categoryHours =
                relevantCategories.Contains(category)
                ? HoursToReportPerCategory : 0.0;
            sb.Append("," + categoryHours.ToString(durationFormat));
        }
        sb.Append(",," +
            HoursWithinReportingPeriod.ToString(durationFormat));
        sb.Append(",," + start.ToString("MM/dd/yyyy"));
        sb.Append("," + start.ToString("HH:mm"));
        sb.Append("," + end.ToString("MM/dd/yyyy"));
        sb.Append("," + end.ToString("HH:mm"));
        sb.Append("," + duration.ToString(durationFormat));

        return sb.ToString();
    }

    public string Subject { get { return subject; } }

    /// <summary>Gets the starting time of the appointment, clipped
    /// to the start date of the report.</summary>
    public DateTime ClippedStart
    {
        get
        {
            return (start > report.Start) ? start : report.Start;
        }
    }

    /// <summary>Gets the ending time of the appointment, clipped
    /// to the end date of the report.</summary>
    public DateTime ClippedEnd
    {
        get
        {
            return (end < report.End) ? end : report.End;
        }
    }

    public double HoursWithinReportingPeriod
    {
        get
        {
            return (ClippedEnd - ClippedStart).TotalHours;
        }
    }

    public HashSet<string> RelevantCategories
    {
        get { return relevantCategories; }
    }

    public double HoursToReportPerCategory
    {
        get
        {
            return (this.relevantCategories.Count > 0) ?
                HoursWithinReportingPeriod / this.relevantCategories.Count : 0;
        }
    }
}

ScheduleReport Inner Class

The ScheduleReport class, shown in the following code, collects the appointment data, calculates subtotals, and generates the report data. The ScheduleReport.Add method allows the ScheduleReportGenerator class to add a ScheduleItem to the report. The GenerateReport and associated helper methods contain the business logic for tabulating the appointment data.

Again, if you are adapting this add-in for use in your environment, you will want to review the business logic and update it appropriately for your scenario. However, within this class, the appointment data is abstracted away from the original Outlook interop classes.

/// <summary>Represents the data and logic for generating a time-
/// tracking report.</summary>
internal class ScheduleReport
{
    /// <summary>The header row for appointment information, in CSV
    /// format.</summary>
    private readonly string csvHeader;

    private DateTime reportStart;
    private DateTime reportEnd;
    private List<string> reportingCategories;
    private List<ScheduleItem> items;
    private bool includeDailySubtotals;

    /// <summary>Initializes a new time tracking report.</summary>
    /// <param name="reportStart">The beginning date and time for the
    /// report.</param>
    /// <param name="reportEnd">The ending date and time for the
    /// report.</param>
    /// <param name="categories">The Outlook categories for which to 
    /// collect data.</param>
    /// <param name="includeDailySubtotals">Indicates whether to
    /// include daily subtotals in the generated report.</param>
    public ScheduleReport(DateTime reportStart, DateTime reportEnd,
        HashSet<string> reportingCategories, bool includeDailySubtotals)
    {
        this.reportStart = reportStart;
        this.reportEnd = reportEnd;

        this.reportingCategories =
            new List<string>(reportingCategories);
        this.reportingCategories.Sort();

        this.items = new List<ScheduleItem>();

        this.includeDailySubtotals = includeDailySubtotals;

        // Construct the CSV header string.
        StringBuilder sb = new StringBuilder("Categories");
        foreach (string category in reportingCategories)
        {
            sb.AppendFormat(",{0}", CsvEscape(category));
        }
        sb.Append(
            ",,Reported Hours,,Start Date,Start Time,End Date,End Time,Duration");
        csvHeader = sb.ToString();
    }

    //...

    public void Add(ScheduleItem item)
    {
        if (item != null)
        {
            items.Add(item);
        }
    }

    internal void CreateCsvFile(string filepath)
    {
        try
        {
            using (TextWriter writer = new StreamWriter(filepath))
            {
                GenerateReport(writer);
            }
        }
        catch
        {
            // Insert exception handling code here.
        }
    }

    private void GenerateReport(TextWriter writer)
    {
        writer.WriteLine("Hours by Category from {0} through {1}",
            reportStart, reportEnd);
        writer.WriteLine();
        writer.WriteLine(csvHeader);
        writer.WriteLine();
        if (items.Count > 0)
        {
            Dictionary<string, double> grandTotals =
                new Dictionary<string, double>();
            foreach (string category in reportingCategories)
            {
                grandTotals[category] = 0.0;
            }

            Dictionary<int, Dictionary<string, double>> dailySubtotals =
                new Dictionary<int, Dictionary<string, double>>();
            for (int dayOffset = 0;
                dayOffset <= (reportEnd - reportStart).Days; dayOffset++)
            {
                dailySubtotals[dayOffset] = new Dictionary<string, double>();
                foreach (string category in reportingCategories)
                {
                    dailySubtotals[dayOffset][category] = 0.0;
                }
            }

            foreach (ScheduleItem item in items)
            {
                AddItemHoursToTotals(grandTotals, dailySubtotals, item);
            }

            WriteReportData(writer, grandTotals, dailySubtotals);
        }
        else
        {
            writer.WriteLine("No items");
        }
        writer.Close();
    }

    private void WriteReportData(
        TextWriter writer,
        Dictionary<string, double> grandTotals,
        Dictionary<int, Dictionary<string, double>> dailySubtotals)
    {
        double totalHoursInReportedInPeriod =
            grandTotals.Sum(x => x.Value);

        WriteTotalsForPeriod(
            writer, grandTotals, totalHoursInReportedInPeriod);
        WritePercentsPerPeriod(
            writer, grandTotals, totalHoursInReportedInPeriod);

        List<int> dayOffsets = new List<int>(dailySubtotals.Keys);
        dayOffsets.Sort();
        if (includeDailySubtotals)
        {
            WriteDailySubtotals(writer, dailySubtotals, dayOffsets);
        }

        writer.WriteLine();
        WriteAppointmentDetails(writer);
    }

    //...
    }
}

This add-in is meant to demonstrate some key operations:

• How to get a set of appointments from the calendar, including recurring appointments, for a specific time frame.

• How to get the set of categories that are available in the Outlook session.

• How to get the set of categories that are assigned to an appointment item.

This add-in reports the time that is spent in appointments, based on the categories assigned to them. This methodology assumes that you create appointments in your default calendar to reflect the time that is spent on various tasks. It also assumes that you assign categories to those appointments to signify the categories of time that you want to include in the report. The report is written to a file in CSV format.

To generate accurate reports by using this time reporting tool, a good practice is to represent each period of time you want to report on by one appointment and not multiple overlapping appointments. For example, if you spend an hour on work that applies to categories A and B, create one appointment for that hour and assign the appointment with categories A and B. Do not use two separate appointments for that same hour, one for category A, and the other for category B. For more information, see the Considerations for Tracking Appointments section.

Example Appointments

For this article, assume that you have several appointments on your calendar for the week of November 1, 2010, through November 5, 2010:

• A recurring weekday appointment from 8:00 A.M. to 8:30 A.M., titled Check messages, and assigned to category Email. The initial occurrence begins before the reporting interval and has no end date.

• An all-day appointment on Monday, November 1, titled Snowshoeing, and assigned to category Vacation.

• An appointment on Tuesday, November 2, from 9:00 A.M. to 12:00 P.M., titled Write agenda and project plan, and assigned to categories Promo planning and Meetings.

• An appointment from Tuesday, November 2, from 1:00 P.M. through Wednesday, November 3, 12:00 P.M., titled Sales training, and assigned to category Training.

• An appointment on Wednesday, November 3, from 1:00 P.M. to 4:00 P.M., titled Continue project plan, and assigned to category Promo planning.

• An appointment on Thursday, November 4, from 9:00 A.M. to 12:00 P.M., titled Begin first article, and assigned to categories Promo writing and Meetings.

• An appointment on Friday, November 5, from 9:00 A.M. to 12:00 P.M., titled Wrap up first article, and assigned to category Promo Writing.

The resulting week in your calendar should resemble Figure 1.

Accessing the Tool

To access the tool, click the Generate Report button on the Add-Ins ribbon. The reporting tool displays a Windows Form (shown in Figure 2) that lets you select the date range for the report, the categories to include in the report, and whether to include daily subtotals.

To reproduce the report from the video, first add the example appointments to your calendar. Then access the tool and enter the following on the form:

• A From Date of November 1, 2010.

• A To Date of November 5, 2010.

• Under Select Categories for Report, select Email, Meetings, Promo Planning, Sick Day, Training, and Vacation, and leave Promo Writing and any other categories cleared.

• Select Include Daily Subtotals in Report, and then click Generate Report.

The tool displays a Select Output File dialog box (shown in Figure 3), and saves the report in CSV format.

Reviewing the Report

To examine the report, start Excel and open the .csv file. The report data does not contain formatting, because it was saved in CSV format. In Figure 4, the first few cells of the first row have been merged, and the columns have been expanded to make the report more readable.

The report has columns for each selected category. The report contains both period and daily totals for each category, in addition to a list of the individual appointments. Appointment detail is grouped by the appointment's start date, so the two day "Sales training" appointment appears under 11/02/2010.

If an item has multiple categories assigned to it, the appointment hours are divided equally between the assigned categories, as can be seen for the Write agenda and project plan task on November 2.

There is one issue about how Outlook handles categories that this article does not address. If a user assigns a category to an appointment, and later deletes the category but does not update the associated appointment, the set of categories that the Outlook session returns does not contain that particular category. However, the appointment item still reports that category as one of the categories assigned to the item.

Considerations for Additional Business Logic

The add-in makes some simplistic assumptions about the business logic for generating the report, and you will have to review and adapt the business logic before you use this tool. Here are some of the assumptions, together with some of the artifacts they generate.

• The business day start and end times are not considered, so all-day events are reported as 24 hours, and multiple-day events similarly overreport hours.

• The add-in does not check for overlapping appointments. Therefore, if two appointments overlap, they both report hours for the overlapping period.

• The add-in does account for appointments that have multiple categories assigned, but it splits the time evenly between the categories for the appointment.

• The sample code for this project contains minimal error checking and exception handling. As with most sample code, you will have to add the appropriate code to make the tool robust.

Considerations for Tracking Appointments

Because the add-in does not account for overlapping calendar items, it reports 24.5 hours for November 1. The full-day appointment Snowshoeing is 24 hours long, and the recurring Check messages item is also reported for the day, accounting for the additional half hour. To avoid this issue in general, do not include two or more overlapping appointments that contain categories on which you report. For example, if you would not include the vacation category in your report, this example would only show 0.5 hours for November 1. Alternatively, if you cancel the single occurrence of Check messages on November 1 on your calendar, this example would show 24 hours for that day.

If you decide to use this add-in without modifications, a good practice is to avoid overlapping appointments that contain categories on which you are reporting. If you have a period during which you have to schedule multiple tasks, consider creating one appointment and assigning multiple categories to it, instead of creating multiple concurrent appointments, each with its own set of categories.

Another adaptation would be not to use full-day or multiple-day appointments for time that you want to accurately track. For example, the Sales training appointment spans from 1:00 P.M, Tuesday, November 2, through noon on Wednesday, November 3. The add-in reports 11 hours for this appointment on November 2, and 12 hours on November 3, including non-business hours during which the training would likely not occur. Instead, track this event as two separate appointments that include only the hours in which you were in training.

Length: 10:16

Grab the Code

• A Simple Macro to Track Your Time in the Outlook Calendar

• Office Client Developer Content

• How to: Filter Recurring Appointments and Search for a String in the Subject

• How to: Search and Obtain Appointments in a Time Range

• Restrict(String)

• Outlook Developer Center

• Office Developer Center on MSDN

• Office Client Developer Content