I have a spreadsheet I'm using at work to keep track of the tasks I work on while I'm working. I want to make a new sheet with a dashboard of sorts that summarizes what I worked on over a date range that I can specify. The source data is formatted in a table like so:
Date Parent Task Sub Task Clock in Clock out Total time Filtered? 4/28/19 Project West Order Supplies 8:50AM 9:00AM 0:10 TRUE 4/28/19 Miscellaneous Meetings 9:00AM 10:00AM 1:00 TRUE 4/28/19 Project West Order Supplies 10:00AM 10:20AM 0:20 TRUE 4/28/19 Day dreaming When is it lunchtime? 10:21AM 12:00AM 1:39 TRUE 4/29/19 Project Timesuck Conference call 3:00PM 5:00PM 1:00 FALSE
On the Dashboard worksheet, I have a Start Date field and an End Date field that I can use to filter the table (using the last column of the source data). I'd like to make a table that shows how much time I spent on each task each day. I'm not exactly sure how to set it up; whether to use a pivot table or a manual table filled with long formulas with Indexes, Offsets, Matches etc, or if this is better done with a macro. I'm the most unfamiliar with Pivot tables, so I'm kinda hoping there's a way to make it work with a pivot table but the closest I've come is to make a table that shows Dates in the Columns category, Parent Tasks in the Rows category and Total time in the Values category. This is close but it's not quite what I'm looking for.
What I'd like is this: I'd like to have the dates running across the top and have two columns under each date; one with a list of the unique tasks I worked on that day (no repeats) and the second column showing how much time I worked on that task. (I know the two column-thing is the complicated/hard part so I'm open to suggestions)
So for instance, the entry for 4/28/19 would look like this:
4/28/19
Project West 0:30 Miscellaneous 1:00 Day dreaming 1:39
I want to extend this into a calendar-type view so I can see at a glance what I've been working on recently.
Any ideas?
Bookmarks