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 that the activity was reported
Activity duration
The data work 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 | Activity Date | Report Date | 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 (mon) | 8 | 8 |
| Day 2 (tue) | 8 | 8 |
| Day 3 (wed) | 8 | 8 |
| Day 4 (thu) | 8 | 8 |
| Day 5 (fri) | 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 (mon) | 8 | 0 |
| Day 2 (tue) | 8 | 0 |
| Day 3 (wed) | 8 | 0 |
| Day 4 (thu) | 8 | 0 |
| Day 5 (fri) | 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 anf the pivot table cant summarize the data in such a way?
Thanks in advance!
