 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Outlook / Calendaring / November 2007How can I total on columns in a view?
I have a simple view which has two columns. Subject and duration. I am grouping on Subject. What I am trying to accomplish is this: I want total hours worked on each Subject. My subjects are project numbers, such as P-12345 and P-45678. I want the view to show P-12345 | 10 hours. Inside that ten hours may be 5 different appointments, all with the Subject P-12345. My view does properly group appointments by Subject, but surprisingly I see no option in Outlook to total on a numeric field, such as duration. This means I have to manually add up all the individual durations to get a total duration. Does anybody have any ideas short of exporting my calendar information into Access? The alternative is to export to Excel and run a pivot table. Outlook can't do math like that in views.  SignatureSue Mosher, Outlook MVP Author of Microsoft Outlook 2007 Programming: Jumpstart for Power Users and Administrators http://www.outlookcode.com/article.aspx?id=54 >I have a simple view which has two columns. Subject and duration. I am > grouping on Subject. What I am trying to accomplish is this: I want total [quoted text clipped - 8 lines] > Does anybody have any ideas short of exporting my calendar information into > Access? Could I write a Macro within Outlook which at the click of the button could generate a simple report for me? > The alternative is to export to Excel and run a pivot table. Outlook can't do math like that in views. > [quoted text clipped - 10 lines] > > Does anybody have any ideas short of exporting my calendar information into > > Access? In theory yes, but that depends, of course, on your VBA skills. :) There are some links to Outlook export code at http://www.outlookcode.com/article.aspx?ID=23 that should get you on the right track. SignatureSue Mosher, Outlook MVP Author of Microsoft Outlook 2007 Programming: Jumpstart for Power Users and Administrators http://www.outlookcode.com/article.aspx?id=54 > Could I write a Macro within Outlook which at the click of the button could > generate a simple report for me? [quoted text clipped - 13 lines] >> > Does anybody have any ideas short of exporting my calendar information into >> > Access? I have been looking for exactly this functionality several months ago, but haven't found a satisfactory answer either, so my reply is only to support your point. In my opinion MS should add totaling on a duration (or other numeric) as a standard feature in Outlook view customization. Exporting to Excel is no option, as you have to do a lot of manual processing each time you want to make such a simple total. SignatureThink, then move > I have a simple view which has two columns. Subject and duration. I am > grouping on Subject. What I am trying to accomplish is this: I want total [quoted text clipped - 8 lines] > Does anybody have any ideas short of exporting my calendar information into > Access? To add it in excel, create a custom view with a custom field that uses a formula that removes the text or converts everything to minutes so you can easily calculate it. (try using Trim([Duration]) - it works for here in a quickie test). Then copy and paste from Outlook into Excel (its easier than using export) - the duration fields will total easily. If you have no idea how to do this, there is a step by step (using a different formula) at http://www.outlook-tips.net/beginner/remindertime.htm - use the formula above instead. also, I believe there are sample forms and formulas on outlookcode.com that total duration. SignatureDiane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Subscribe to Exchange Messaging Outlook newsletter: EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM >I have been looking for exactly this functionality several months ago, but > haven't found a satisfactory answer either, so my reply is only to support [quoted text clipped - 22 lines] >> into >> Access? IThanks, Diane. I already had done the custom field before, but not though about the copy instead of the export. However, I still have to add the sum formula for each category, each time I do a copy to Excel again, correct? This is what Iam trying to avoid or automate. SignatureThink, then move > To add it in excel, create a custom view with a custom field that uses a > formula that removes the text or converts everything to minutes so you can [quoted text clipped - 36 lines] > >> into > >> Access? yes, you'll need to either manually sum it or write a macro in excel to do it for you. SignatureDiane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Subscribe to Exchange Messaging Outlook newsletter: EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM > IThanks, Diane. I already had done the custom field before, but not > though [quoted text clipped - 57 lines] >> >> into >> >> Access? Hello Diane, Triggered by your input, I designed a very easy way to resolve my specific problem, i.e. totalling my time spend per project (category), based on my calendar entries. No macros nor VBA needed. 1. Make in the Calendar a view 'Time spend' based on 'By Category view' Add (formula) fields Minutes, Hours, Days, Month (and possibly Year) Minutes = Left([Duration],3) Hours = [Minutes]/60 Days = [Hours]/8 Month = Month([Start]) Only Month, Start, Days to be displayed inthe view. 2. Select / Copy all lines to Excel workbook 3. Create pivot table (1x, when creating the workbook), with Month ascolumn header, Category als row header, and Sum of Days as data field. NB select as source data not de specific rows, but the columns in which the data are. 4. Based on the pivot table you can then (1x) create a stacked column chart, showing total time spend / project (and month), and a pie chart showing which % of your time goes to which project. All you need to do periodicall (e.g. weekly) is: - go to view Time Spend in Calendar - select / copy all rows to the Excel workbook - refresh pivot table, and all figures and graphs are automatically updated! SignatureThink, then move > yes, you'll need to either manually sum it or write a macro in excel to do > it for you. [quoted text clipped - 60 lines] > >> >> into > >> >> Access? Cool. Thanks for letting us know... it may help others. SignatureDiane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Subscribe to Exchange Messaging Outlook newsletter: EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM > Hello Diane, > [quoted text clipped - 111 lines] >> >> >> into >> >> >> Access?
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.