+ Reply to Thread
Results 1 to 4 of 4

Pivot table summarizing time spent per task per day from a task log

  1. #1
    Registered User
    Join Date
    09-30-2011
    Location
    California, USA
    MS-Off Ver
    Excel 2003
    Posts
    19

    Pivot table summarizing time spent per task per day from a task log

    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?

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Pivot table summarizing time spent per task per day from a task log

    Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    09-30-2011
    Location
    California, USA
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: Pivot table summarizing time spent per task per day from a task log

    Thanks for the reply, please see attached.
    Attached Files Attached Files

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Pivot table summarizing time spent per task per day from a task log

    Sorry, I am unable to improve on your PT. Maybe someone else has a better scheme.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. trying to create a button that calculates time spent on a task
    By CalamL in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-05-2019, 03:55 AM
  2. [SOLVED] Pivot Chart: Task spend over time, +comparison w/ Task Budget
    By mike_302 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 12-28-2018, 04:21 PM
  3. need function to calculate time spent on the given task
    By Giri.hb in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-28-2015, 04:44 AM
  4. [SOLVED] inputting a number representing "Time" spent on a "Task" under drop menu's
    By cubbymonkey in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-09-2014, 04:03 PM
  5. Replies: 0
    Last Post: 11-17-2013, 10:21 AM
  6. Replies: 6
    Last Post: 02-28-2012, 10:50 AM
  7. [SOLVED] Pivot Table through Scheduled Task
    By [email protected] in forum Excel General
    Replies: 1
    Last Post: 10-18-2005, 05:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1