Thread
:
How can I total on columns in a view?
View Single Post
#
9
November 5th 07, 10:17 AM posted to microsoft.public.outlook.calendaring
Felix
external usenet poster
Posts: 3
How can I total on columns in a view? - Time spend per project
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?
Felix
View Public Profile
View message headers
Find all posts by Felix
Find all threads started by Felix
Ads