How can I total on columns in a view?

[Guest]

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?

 

[Sue Mosher [MVP-Outlook]]

The alternative is to export to Excel and run a pivot table. Outlook can't do math like that in views.

 

[Guest]

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]]

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.

--
Sue Mosher, Outlook MVP
Author of Microsoft Outlook 2007 Programming:
Jumpstart for Power Users and Administrators
http://www.outlookcode.com/article.aspx?id=54

 

[Guest]

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.

 

[Diane Poremsky [MVP]]

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.

 

[Guest]

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

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]]

yes, you'll need to either manually sum it or write a macro in excel to do
it for you.

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

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.

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


:

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?

 

[Guest]

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

yes, you'll need to either manually sum it or write a macro in excel to do
it for you.

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

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.












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


:

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?

 

[Diane Poremsky]

Cool. Thanks for letting us know... it may help others.

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

yes, you'll need to either manually sum it or write a macro in excel to
do
it for you.

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


:

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.












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


:

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?