• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3318
  • Last Modified:

Calculating hours per week under specific categories in Outlook Calendar

To keep track of my work week & hours done - is it possible to either in Outlook (2007) now or even via some sort of plugin to have Outlook calculate & report as needed the total amount of hours for a specific category in the Outlook Calendar?
The 2 main categories I use are "Work" and "Remote".
It would be really useful to calculate this on demand for a day, week, month or even over a year if need be.
  • 5
  • 5
  • 2
1 Solution
David LeeCommented:
Hi, kiwistag.

What sort of output are you looking for?
kiwistagAuthor Commented:
I suppose some sort of Excel spreadsheet (CSV) but format would be the interesting part.
I know what I want but not how I want it, I suppose it all depends on what is available.

David LeeCommented:
I assume it needs to report on tasks, right?
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

kiwistagAuthor Commented:
Categories may be easiest.
I talked to a Microsoft Solution Specialist & he recommended getting an API designed for it - am yet to check with him & see if someone has already built somethign suitable.
David LeeCommented:
I don't know what they mean by "API designed".  Outlook already has an API.  This just requires a script.  It's simple enough to do this, I just need to know what sort of output you're looking for.  Do you just want the total, do you want to list each item, etc.  If the former, then will a pop-up dialog-box reporting the total be sufficient?
kiwistagAuthor Commented:
Sorry, late nites & wrong terminology.
Basically all I'm after is the total for a week (or month) linked to a category. Lists of each item aren't really necessary as most info is within each time period/meeting/appointment and the title just vaguely references the category anyway.

David LeeCommented:
Here is a macro that sums the time for the two categories "Remote" and "Work".  It displays the output in pop-up dialog box.  Follow these instructions to use it.

1.  Start Outlook
2.  Click Tools > Macro > Visual Basic Editor
3.  If not already expanded, expand Microsoft Office Outlook Objects
4.  If not already expanded, expand Modules
5.  Select an existing module (e.g. Module1) by double-clicking on it or create a new module by right-clicking Modules and selecting Insert > Module.
6.  Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook's VB Editor window
7.  Edit the code as needed.  I included comments wherever something needs to or can change
8.  Click the diskette icon on the toolbar to save the changes
9.  Close the VB Editor
10. Run the macro Sum4Category
Sub Sum4Category()
    Const MACRO_NAME = "Sum for Category"
    Dim olkItems As Outlook.Items, _
        olkSelected As Outlook.Items, _
        olkAppt As Outlook.AppointmentItem, _
        datStart As Date, _
        datEnd As Date, _
        dblRemoteHours As Double, _
        dblWorkHours As Double
    datStart = InputBox("Starting date?", MACRO_NAME, Date)
    datEnd = InputBox("Ending date?", MACRO_NAME, Date + 7)
    If IsDate(datStart) And IsDate(datEnd) Then
        Set olkItems = Session.GetDefaultFolder(olFolderCalendar).Items
        olkItems.IncludeRecurrences = True
        olkItems.Sort "Start"
        Set olkSelected = olkItems.Restrict("[Start] >= '" & datStart & "' AND [Start] <= '" & datEnd & "'")
        For Each olkAppt In olkSelected
            If InStr(1, olkAppt.Categories, "Remote") Then
                dblRemoteHours = dblRemoteHours + DateDiff("n", olkAppt.Start, olkAppt.End)
            End If
            If InStr(1, olkAppt.Categories, "Work") Then
                dblWorkHours = dblWorkHours + DateDiff("n", olkAppt.Start, olkAppt.End)
            End If
        msgbox "You must enter valid starting and ending dates to run this macro.", vbCritical + vbOKOnly, MACRO_NAME
    End If
    Set olkAppt = Nothing
    Set olkSelected = Nothing
    Set olkItems = Nothing
    dblRemoteHours = dblRemoteHours / 60
    dblWorkHours = dblWorkHours / 60
    msgbox "Total time for category Remote = " & dblRemoteHours & vbCrLf & "Total time for category Work = " & dblWorkHours, vbInformation + vbOKOnly, MACRO_NAME
End Sub

Open in new window

kiwistagAuthor Commented:
Works a charm - thanks :)

kiwistagAuthor Commented:
Excellent result :)
Thanks again :)
David LeeCommented:
You're welcome.  Glad I could help.
I am hoping someone might be able to tell me why this macro was working perfectly on Friday and now, Saturday, it isn't working? I am getting a runtime error 13: type mismatch.

Can someone help me? I am a novice with Visual Basic.
Nevermind I figured it out. I was using the wrong date format.  Thanks.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now