+ Reply to Thread
Results 1 to 6 of 6

Help analysing a timesheet database

  1. #1
    Registered User
    Join Date
    07-22-2021
    Location
    Campinas, Brazil
    MS-Off Ver
    2016
    Posts
    7

    Help analysing a timesheet database

    Hello community!

    I am having trouble analysing a set of timesheet data made of the following columns:
    User
    Activity Type
    Date the activity was performed
    Date the activity was reported
    Activity duration

    The data works as follows:
    If user "X" spent 2 hours in a meeting with a client in 01/01/2021, and included this information on his timesheet only in 01/03/2021, the row regarding this activity would show, respectively:
    X
    Meeting
    01/01/2021
    01/03/2021
    2

    I want to summarize this data in a 3 column table:
    The first one, is "Date";
    The second is "Ammount of hours worked on said date"; and
    The third is "Ammount of hours reported on said date".

    To illustrate this, consider a user that works 8 hours per day, monday to friday, and reports 8 hours per day. His timesheet and his summarized data should look like this:

    User Activity Type Date Activity Date Reported Duration
    X example day 1 day 1 8
    X example day 2 day 2 8
    X example day 3 day 3 8
    X example day 4 day 4 8
    X example day 5 day 5 8


    Date Hours Worked Hours Reported
    day 1 8 8
    day 2 8 8
    day 3 8 8
    day 4 8 8
    day 5 8 8
    Now consider a user that works the same 8 hours per day, but reports them all only on friday. His timesheet and his summarized data should look like this:

    User Activity Type Date Activity Date Reported Duration
    X example day 1 day 5 8
    X example day 2 day 5 8
    X example day 3 day 5 8
    X example day 4 day 5 8
    X example day 5 day 5 8


    Date Hours Worked Hours Reported
    day 1 8 0
    day 2 8 0
    day 3 8 0
    day 4 8 0
    day 5 8 40
    The problem is that i cant seem to create a pivot table summarizing the data in this way. As i need to be able to use the columns "User" and "Activity Type" as filters, I assume that the pivot table is the best option to achieve this.

    Am I correct in my assumption? If so, how can I achieve my objective?
    Or am I wrong and the pivot table cant summarize the data in such a way?

    Thanks in advance!

  2. #2
    Valued Forum Contributor
    Join Date
    06-23-2021
    Location
    Romania
    MS-Off Ver
    2021, 365 v 2208
    Posts
    722

    Re: Help analysing a timesheet database

    Can you, please, upload a sample workbook?
    Diana Tanase


    If the solutions offered helped you to solve your problem, then mark the thread as SOLVED (thread tools in the top menu) and you can click on * to add reputation to those who helped you, as a way to say thank you !

  3. #3
    Registered User
    Join Date
    07-22-2021
    Location
    Campinas, Brazil
    MS-Off Ver
    2016
    Posts
    7

    Re: Help analysing a timesheet database

    tanasedn,

    It will be my pleasure. TYSM for taking a crack at this.

    Let me know if the sample and the original explanation provided were enough.
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    06-23-2021
    Location
    Romania
    MS-Off Ver
    2021, 365 v 2208
    Posts
    722

    Re: Help analysing a timesheet database

    Hi,
    I introduced, in the original table, a new column, in which I calculated the duration (in hours). Then I introduced a new column in which I entered the reported week. In the pivot table you can enter, per user, the week and the days worked with the sum by the number of hours.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,958

    Re: Help analysing a timesheet database

    Added 3 columns to your data. 2 columns to convert the dates to days and 1 column convert the duration in hours.

    PivotTable is created as your first table. Then the second table is created using formulas based on this PivotTable.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    07-22-2021
    Location
    Campinas, Brazil
    MS-Off Ver
    2016
    Posts
    7

    Re: Help analysing a timesheet database

    tanasedn and josephteh, thank you so much for the help!

    One of the greatest difficulties of this problem is compiling a column of unique dates, derived from the 2 columns "Date activity" and "date reported"
    Even the new UNIQUE and FILTER formulas couldnt manage "appending" two columns into one. The only tool I could find that achieved this was using PowerQuery to unpivot the 2 date columns into one, and then isolate it, remove duplicates and sort by ascending.

    With this list, I could SUMIF the values based on the PowerQuery list of unique dates and wheather the value was tied to a "Activity Date" of a "Report Date".
    The problem with that is that i completely lost the ability to use the fields "User" and "Activity Type" as filters.

    josephteh's idea of using the pivot table as an intermediate table was the piece I was missing. I duplicated the columns "User" and "Activity Type" and recreated the dataset as a pivot table, using the duplicated columns as filters.
    Now that the pivot table could filter out unwanted values for the sum, I was able to SUMIF only the filtered values on the pivot table.
    This allowed me to achieve exactly what I wanted.

    I attached a modified version of josephteh's file with what I have done.

    Thank you all for the priceless help!

    I still wonder if using PQ is the only way to solve this, as this limits the ability of the worksheet to be functional on excel online.
    Any toughts on this?
    Attached Files Attached Files
    Last edited by JoaoNassif; 07-27-2021 at 10:30 AM.

+ 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. database in timesheet driver details
    By langeloo in forum Excel General
    Replies: 2
    Last Post: 12-02-2018, 10:14 PM
  2. Cross Analysing data
    By Mattg121 in forum Excel General
    Replies: 1
    Last Post: 01-20-2015, 07:08 AM
  3. Analysing database information to create graphs
    By Miss_Fidget in forum Excel General
    Replies: 1
    Last Post: 06-15-2009, 06:28 AM
  4. guidance in analysing a database
    By Claudia_MBA_Student in forum Excel General
    Replies: 4
    Last Post: 04-13-2009, 06:58 PM
  5. Importing and Analysing xls Files
    By daguirrem in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 11-14-2008, 11:24 AM
  6. Analysing data
    By i_ujala in forum Excel General
    Replies: 1
    Last Post: 04-08-2007, 05:29 AM
  7. [SOLVED] Analysing data through pivot
    By Chris in forum Excel General
    Replies: 3
    Last Post: 01-09-2006, 11:45 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