![]() |
If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Search this Thread | Display Modes |
|
#1
|
|||
|
|||
![]()
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? |
#3
|
|||
|
|||
![]()
Could I write a Macro within Outlook which at the click of the button could
generate a simple report for me? "Sue Mosher [MVP-Outlook]" wrote: The alternative is to export to Excel and run a pivot table. Outlook can't do math like that in views. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook 2007 Programming: Jumpstart for Power Users and Administrators http://www.outlookcode.com/article.aspx?id=54 "Jason R. Senior" Jason R. wrote in message ... 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? |
#4
|
|||
|
|||
![]()
In theory yes, but that depends, of course, on your VBA skills.
![]() -- Sue Mosher, Outlook MVP Author of Microsoft Outlook 2007 Programming: Jumpstart for Power Users and Administrators http://www.outlookcode.com/article.aspx?id=54 "Jason R. Senior" wrote in message ... Could I write a Macro within Outlook which at the click of the button could generate a simple report for me? "Sue Mosher [MVP-Outlook]" wrote: The alternative is to export to Excel and run a pivot table. Outlook can't do math like that in views. "Jason R. Senior" Jason R. wrote in message ... 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? |
#5
|
|||
|
|||
![]()
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. -- Think, then move "Jason R. Senior" wrote: 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? |
#6
|
|||
|
|||
![]()
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. -- Diane 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: Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Subscribe to Exchange Messaging Outlook newsletter: "Felix" wrote in message ... 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. -- Think, then move "Jason R. Senior" wrote: 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? |
#7
|
|||
|
|||
![]()
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. -- Think, then move "Diane Poremsky [MVP]" wrote: 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. -- Diane 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: Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Subscribe to Exchange Messaging Outlook newsletter: "Felix" wrote in message ... 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. -- Think, then move "Jason R. Senior" wrote: 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? |
#8
|
|||
|
|||
![]()
yes, you'll need to either manually sum it or write a macro in excel to do
it for you. -- Diane 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: Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Subscribe to Exchange Messaging Outlook newsletter: "Felix" wrote in message ... 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. -- Think, then move "Diane Poremsky [MVP]" wrote: 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. -- Diane 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: Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Subscribe to Exchange Messaging Outlook newsletter: "Felix" wrote in message ... 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. -- Think, then move "Jason R. Senior" wrote: 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? |
#9
|
|||
|
|||
![]()
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! -- Think, then move "Diane Poremsky [MVP]" wrote: yes, you'll need to either manually sum it or write a macro in excel to do it for you. -- Diane 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: Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Subscribe to Exchange Messaging Outlook newsletter: "Felix" wrote in message ... 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. -- Think, then move "Diane Poremsky [MVP]" wrote: 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. -- Diane 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: Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Subscribe to Exchange Messaging Outlook newsletter: "Felix" wrote in message ... 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. -- Think, then move "Jason R. Senior" wrote: 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? |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
how can i get a total # of contacts in the phone list view | Betty | Outlook - Using Contacts | 1 | November 6th 06 09:44 PM |
In the week view can I split each day in to 3 columns? | volleykid | Outlook - Calandaring | 5 | October 17th 06 02:46 AM |
Outlook appts should be able to have columns to view alot of info | Gone to Maui | Outlook - Calandaring | 0 | May 19th 06 01:15 PM |
How do I view a total count of contacts in a Distribution List | Irish Mary | Outlook - Using Contacts | 2 | March 16th 06 11:31 PM |
Adding custom fields as columns in the current view | Simon Gray | Outlook - Using Contacts | 1 | January 18th 06 05:59 PM |