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 | Display Modes |
#1
|
|||
|
|||
How to generate simple reports from a time log?
I use an Excel 2007 table to keep a log of daily activities. (See the
simplified example below). I want to construct for my own use a simple reporting system that will quickly display the answers to such queries as this: Show me the total hours I spent on each activity on 2009-11-13. Show me how many hours I spent on running last week (or last month). Show me a week-by-week breakdown since the beginning of the year of my totals for writing and music. How can I do this with minimum fuss? I don't expect anyone to walk me through it step by step, but it would be helpful to know what tools will be useful: PivotTables? Macros? I don't want to have to enter formulas repeatedly. Ideally I'd like to be able to make choices from drop-down lists or menus. I'd welcome any guidance on the basic approach, as well as web addresses for any useful demos or existing samples I can use as models. Thanks. David THE DATA SOURCE: DATE TASK HOURS 2009-11-13 music 1.5 2009-11-13 running 1.0 2009-11-13 music 0.7 2009-11-13 work 5.2 2009-11-13 work 2.0 2009-11-13 writing 2.5 2009-11-14 work 3.2 2009-11-14 writing 2.3 2009-11-14 music 1.6 2009-11-15 running 1.2 2009-11-15 work 4.5 2009-11-15 music 2.0 2009-11-15 music 0.5 2009-11-15 writing 2.2 |
#2
|
|||
|
|||
How to generate simple reports from a time log?
i would use autofilters.
-- Gary Keramidas Excel 2003 "Axel07" wrote in message ... I use an Excel 2007 table to keep a log of daily activities. (See the simplified example below). I want to construct for my own use a simple reporting system that will quickly display the answers to such queries as this: Show me the total hours I spent on each activity on 2009-11-13. Show me how many hours I spent on running last week (or last month). Show me a week-by-week breakdown since the beginning of the year of my totals for writing and music. How can I do this with minimum fuss? I don't expect anyone to walk me through it step by step, but it would be helpful to know what tools will be useful: PivotTables? Macros? I don't want to have to enter formulas repeatedly. Ideally I'd like to be able to make choices from drop-down lists or menus. I'd welcome any guidance on the basic approach, as well as web addresses for any useful demos or existing samples I can use as models. Thanks. David THE DATA SOURCE: DATE TASK HOURS 2009-11-13 music 1.5 2009-11-13 running 1.0 2009-11-13 music 0.7 2009-11-13 work 5.2 2009-11-13 work 2.0 2009-11-13 writing 2.5 2009-11-14 work 3.2 2009-11-14 writing 2.3 2009-11-14 music 1.6 2009-11-15 running 1.2 2009-11-15 work 4.5 2009-11-15 music 2.0 2009-11-15 music 0.5 2009-11-15 writing 2.2 |
#3
|
|||
|
|||
How to generate simple reports from a time log?
I would use Pivot Tables. They will do everything you want, and more. The
drop down menus you want are Page Fields. Regards, Fred "Axel07" wrote in message ... I use an Excel 2007 table to keep a log of daily activities. (See the simplified example below). I want to construct for my own use a simple reporting system that will quickly display the answers to such queries as this: Show me the total hours I spent on each activity on 2009-11-13. Show me how many hours I spent on running last week (or last month). Show me a week-by-week breakdown since the beginning of the year of my totals for writing and music. How can I do this with minimum fuss? I don't expect anyone to walk me through it step by step, but it would be helpful to know what tools will be useful: PivotTables? Macros? I don't want to have to enter formulas repeatedly. Ideally I'd like to be able to make choices from drop-down lists or menus. I'd welcome any guidance on the basic approach, as well as web addresses for any useful demos or existing samples I can use as models. Thanks. David THE DATA SOURCE: DATE TASK HOURS 2009-11-13 music 1.5 2009-11-13 running 1.0 2009-11-13 music 0.7 2009-11-13 work 5.2 2009-11-13 work 2.0 2009-11-13 writing 2.5 2009-11-14 work 3.2 2009-11-14 writing 2.3 2009-11-14 music 1.6 2009-11-15 running 1.2 2009-11-15 work 4.5 2009-11-15 music 2.0 2009-11-15 music 0.5 2009-11-15 writing 2.2 |
#4
|
|||
|
|||
How to generate simple reports from a time log?
|
#5
|
|||
|
|||
How to generate simple reports from a time log?
Many thanks, Herbert! This is just what I need.
David "Herbert Seidenberg" wrote: Excel 2007 PivotTable An example: http://www.mediafire.com/file/wnwcznwfnjk/11_14_09.xlsx . |
#6
|
|||
|
|||
How to generate simple reports from a time log?
Hi, Herbert. Thanks again for your very helpful example. I've been trying to
duplicate your PivotTable designs. Your PivotTables come out with headings "Past", "Sloth" and "Sum of Waste" (I admire your candor in choosing these particular names!). "Past" and "Sloth" each has a drop-down button. When I create the PivotTables, however, only one of these gets a drop-down button; when I click on it it takes me to a dialog in which I can select either "Past" or "Sloth" to display the appropriate filtering options. The functionality seems to be the same, but I'd rather have the separate drop-down buttons. What accounts for the difference? David "Herbert Seidenberg" wrote: Excel 2007 PivotTable An example: http://www.mediafire.com/file/wnwcznwfnjk/11_14_09.xlsx . |
#7
|
|||
|
|||
How to generate simple reports from a time log?
David,
In my design, enable Show/Hide Field List. For each PT, study the Row Labels list: You can drag one or more Fields into it, in the desired order. Open your design and compare. Also study the multiple PT source structure. Herb |
#8
|
|||
|
|||
How to generate simple reports from a time log?
Thanks, Herb. I was able to force a dropdown on each of the field labels by
first going to PivotTable Options and checking Classic PivotTable style; then laying out my table; then unchecking Classic style. Awkward, but it works. I'd be interested in studying the "multiple PT source structure", but I don't know what you mean by that. Thanks again, by the way, for creating that model for me that answered all my requirements -- it saved me hours! David "Herbert Seidenberg" wrote: David, In my design, enable Show/Hide Field List. For each PT, study the Row Labels list: You can drag one or more Fields into it, in the desired order. Open your design and compare. Also study the multiple PT source structure. Herb . |
Thread Tools | |
Display Modes | |
|
|