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!
Bookmarks