annieflanz
New Member
- Joined
- Dec 11, 2012
- Messages
- 14
- Office Version
- 365
- Platform
- MacOS
Hello,
I have set of data of time spent on various activities, tasks, and projects for a set of clients over a period of time.
I'm looking to summarize that data to determine how much time was spent each day on each combination of the activity, task, project, and client. I tried a pivot table, but cannot figure out how to get the results in the same column format. The column format is necessary to upload into a specific time tracking/billing program. I'm thinking of something that would remove duplicate entries of the same date, client, project, task, and activity, but that also summarizes the time at the same time.
The example attached here has 50 rows of data, whereas the spreadsheet I am working with has 500,000+ rows of data, so anything too manual wouldn't be effective.
Any way to accomplish this goal? I appreciate any help or suggestions! Thank you!
Here's an example of the data:
Here is an example of the results I am looking for:
I have set of data of time spent on various activities, tasks, and projects for a set of clients over a period of time.
I'm looking to summarize that data to determine how much time was spent each day on each combination of the activity, task, project, and client. I tried a pivot table, but cannot figure out how to get the results in the same column format. The column format is necessary to upload into a specific time tracking/billing program. I'm thinking of something that would remove duplicate entries of the same date, client, project, task, and activity, but that also summarizes the time at the same time.
The example attached here has 50 rows of data, whereas the spreadsheet I am working with has 500,000+ rows of data, so anything too manual wouldn't be effective.
Any way to accomplish this goal? I appreciate any help or suggestions! Thank you!
Here's an example of the data:
| Example.xlsx | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | Date | Client | Project | Task | Activity | Hours | ||
| 2 | 2022-01-01 | Client A | Project X | Task 2 | Activity 2 | 8 | ||
| 3 | 2022-01-01 | Client A | Project Y | Task 3 | Activity 3 | 2 | ||
| 4 | 2022-01-01 | Client A | Project Z | Task 2 | Activity 1 | 7 | ||
| 5 | 2022-01-01 | Client A | Project Z | Task 4 | Activity 3 | 9 | ||
| 6 | 2022-01-01 | Client B | Project X | Task 4 | Activity 2 | 8 | ||
| 7 | 2022-01-01 | Client B | Project X | Task 4 | Activity 3 | 3 | ||
| 8 | 2022-01-01 | Client B | Project Y | Task 2 | Activity 3 | 10 | ||
| 9 | 2022-01-01 | Client B | Project Z | Task 5 | Activity 3 | 2 | ||
| 10 | 2022-01-02 | Client A | Project X | Task 4 | Activity 3 | 8 | ||
| 11 | 2022-01-02 | Client A | Project Y | Task 4 | Activity 1 | 7 | ||
| 12 | 2022-01-02 | Client A | Project Y | Task 4 | Activity 1 | 4 | ||
| 13 | 2022-01-02 | Client A | Project Z | Task 2 | Activity 3 | 4 | ||
| 14 | 2022-01-02 | Client A | Project Z | Task 5 | Activity 3 | 2 | ||
| 15 | 2022-01-02 | Client B | Project Y | Task 5 | Activity 3 | 6 | ||
| 16 | 2022-01-02 | Client C | Project X | Task 4 | Activity 1 | 4 | ||
| 17 | 2022-01-02 | Client C | Project X | Task 5 | Activity 3 | 1 | ||
| 18 | 2022-01-02 | Client C | Project Z | Task 1 | Activity 3 | 9 | ||
| 19 | 2022-01-02 | Client C | Project Z | Task 4 | Activity 1 | 6 | ||
| 20 | 2022-01-02 | Client D | Project X | Task 3 | Activity 1 | 8 | ||
| 21 | 2022-01-02 | Client D | Project Y | Task 5 | Activity 3 | 1 | ||
| 22 | 2022-01-02 | Client D | Project Y | Task 5 | Activity 3 | 8 | ||
| 23 | 2022-01-03 | Client A | Project X | Task 1 | Activity 1 | 8 | ||
| 24 | 2022-01-03 | Client A | Project X | Task 5 | Activity 2 | 3 | ||
| 25 | 2022-01-03 | Client A | Project Y | Task 4 | Activity 3 | 7 | ||
| 26 | 2022-01-03 | Client C | Project X | Task 5 | Activity 2 | 3 | ||
| 27 | 2022-01-03 | Client C | Project Y | Task 2 | Activity 1 | 10 | ||
| 28 | 2022-01-03 | Client D | Project Z | Task 2 | Activity 1 | 1 | ||
| 29 | 2022-01-04 | Client A | Project X | Task 2 | Activity 2 | 1 | ||
| 30 | 2022-01-04 | Client A | Project X | Task 2 | Activity 2 | 6 | ||
| 31 | 2022-01-04 | Client A | Project Y | Task 4 | Activity 3 | 2 | ||
| 32 | 2022-01-04 | Client A | Project Y | Task 5 | Activity 1 | 7 | ||
| 33 | 2022-01-04 | Client A | Project Z | Task 4 | Activity 1 | 6 | ||
| 34 | 2022-01-04 | Client B | Project Y | Task 1 | Activity 2 | 2 | ||
| 35 | 2022-01-04 | Client C | Project Y | Task 4 | Activity 2 | 3 | ||
| 36 | 2022-01-04 | Client C | Project Z | Task 4 | Activity 2 | 3 | ||
| 37 | 2022-01-04 | Client D | Project Z | Task 2 | Activity 3 | 6 | ||
| 38 | 2022-01-05 | Client A | Project Y | Task 2 | Activity 1 | 10 | ||
| 39 | 2022-01-05 | Client A | Project Z | Task 2 | Activity 1 | 5 | ||
| 40 | 2022-01-05 | Client B | Project X | Task 1 | Activity 2 | 4 | ||
| 41 | 2022-01-05 | Client B | Project X | Task 2 | Activity 2 | 2 | ||
| 42 | 2022-01-05 | Client B | Project Y | Task 1 | Activity 1 | 9 | ||
| 43 | 2022-01-05 | Client B | Project Y | Task 5 | Activity 2 | 2 | ||
| 44 | 2022-01-05 | Client B | Project Z | Task 4 | Activity 2 | 9 | ||
| 45 | 2022-01-05 | Client C | Project Y | Task 1 | Activity 1 | 4 | ||
| 46 | 2022-01-05 | Client C | Project Z | Task 4 | Activity 2 | 7 | ||
| 47 | 2022-01-05 | Client D | Project X | Task 1 | Activity 3 | 7 | ||
| 48 | 2022-01-05 | Client D | Project Y | Task 1 | Activity 2 | 3 | ||
| 49 | 2022-01-05 | Client D | Project Y | Task 5 | Activity 3 | 7 | ||
| 50 | 2022-01-05 | Client D | Project Z | Task 3 | Activity 2 | 5 | ||
| 51 | 2022-01-05 | Client D | Project Z | Task 4 | Activity 2 | 8 | ||
Desired Results | ||||||||
Here is an example of the results I am looking for:
| Example.xlsx | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | Date | Client | Project | Task | Activity | Total Hours For Day | ||
| 2 | 2022-01-01 | Client A | Project X | Task 2 | Activity 2 | <TOTAL HOURS FOR DAY> | ||
| 3 | 2022-01-01 | Client A | Project Y | Task 3 | Activity 3 | <TOTAL HOURS FOR DAY> | ||
| 4 | 2022-01-01 | Client A | Project Z | Task 2 | Activity 1 | <TOTAL HOURS FOR DAY> | ||
| 5 | 2022-01-01 | Client A | Project Z | Task 4 | Activity 3 | <TOTAL HOURS FOR DAY> | ||
| 6 | 2022-01-01 | Client B | Project X | Task 4 | Activity 2 | <TOTAL HOURS FOR DAY> | ||
| 7 | 2022-01-01 | Client B | Project X | Task 4 | Activity 3 | <TOTAL HOURS FOR DAY> | ||
| 8 | 2022-01-01 | Client B | Project Y | Task 2 | Activity 3 | <TOTAL HOURS FOR DAY> | ||
| 9 | 2022-01-01 | Client B | Project Z | Task 5 | Activity 3 | <TOTAL HOURS FOR DAY> | ||
| 10 | 2022-01-02 | Client A | Project X | Task 4 | Activity 3 | <TOTAL HOURS FOR DAY> | ||
| 11 | 2022-01-02 | Client A | Project Y | Task 4 | Activity 1 | <TOTAL HOURS FOR DAY> | ||
| 12 | 2022-01-02 | Client A | Project Z | Task 2 | Activity 3 | <TOTAL HOURS FOR DAY> | ||
| 13 | 2022-01-02 | Client A | Project Z | Task 5 | Activity 3 | <TOTAL HOURS FOR DAY> | ||
| 14 | 2022-01-02 | Client B | Project Y | Task 5 | Activity 3 | <TOTAL HOURS FOR DAY> | ||
| 15 | 2022-01-02 | Client C | Project X | Task 4 | Activity 1 | <TOTAL HOURS FOR DAY> | ||
| 16 | 2022-01-02 | Client C | Project X | Task 5 | Activity 3 | <TOTAL HOURS FOR DAY> | ||
| 17 | 2022-01-02 | Client C | Project Z | Task 1 | Activity 3 | <TOTAL HOURS FOR DAY> | ||
| 18 | 2022-01-02 | Client C | Project Z | Task 4 | Activity 1 | <TOTAL HOURS FOR DAY> | ||
| 19 | 2022-01-02 | Client D | Project X | Task 3 | Activity 1 | <TOTAL HOURS FOR DAY> | ||
| 20 | 2022-01-02 | Client D | Project Y | Task 5 | Activity 3 | <TOTAL HOURS FOR DAY> | ||
| 21 | 2022-01-03 | Client A | Project X | Task 1 | Activity 1 | <TOTAL HOURS FOR DAY> | ||
| 22 | 2022-01-03 | Client A | Project X | Task 5 | Activity 2 | <TOTAL HOURS FOR DAY> | ||
| 23 | 2022-01-03 | Client A | Project Y | Task 4 | Activity 3 | <TOTAL HOURS FOR DAY> | ||
| 24 | 2022-01-03 | Client C | Project X | Task 5 | Activity 2 | <TOTAL HOURS FOR DAY> | ||
| 25 | 2022-01-03 | Client C | Project Y | Task 2 | Activity 1 | <TOTAL HOURS FOR DAY> | ||
| 26 | 2022-01-03 | Client D | Project Z | Task 2 | Activity 1 | <TOTAL HOURS FOR DAY> | ||
| 27 | 2022-01-04 | Client A | Project X | Task 2 | Activity 2 | <TOTAL HOURS FOR DAY> | ||
| 28 | 2022-01-04 | Client A | Project Y | Task 4 | Activity 3 | <TOTAL HOURS FOR DAY> | ||
| 29 | 2022-01-04 | Client A | Project Y | Task 5 | Activity 1 | <TOTAL HOURS FOR DAY> | ||
| 30 | 2022-01-04 | Client A | Project Z | Task 4 | Activity 1 | <TOTAL HOURS FOR DAY> | ||
| 31 | 2022-01-04 | Client B | Project Y | Task 1 | Activity 2 | <TOTAL HOURS FOR DAY> | ||
| 32 | 2022-01-04 | Client C | Project Y | Task 4 | Activity 2 | <TOTAL HOURS FOR DAY> | ||
| 33 | 2022-01-04 | Client C | Project Z | Task 4 | Activity 2 | <TOTAL HOURS FOR DAY> | ||
| 34 | 2022-01-04 | Client D | Project Z | Task 2 | Activity 3 | <TOTAL HOURS FOR DAY> | ||
| 35 | 2022-01-05 | Client A | Project Y | Task 2 | Activity 1 | <TOTAL HOURS FOR DAY> | ||
| 36 | 2022-01-05 | Client A | Project Z | Task 2 | Activity 1 | <TOTAL HOURS FOR DAY> | ||
| 37 | 2022-01-05 | Client B | Project X | Task 1 | Activity 2 | <TOTAL HOURS FOR DAY> | ||
| 38 | 2022-01-05 | Client B | Project X | Task 2 | Activity 2 | <TOTAL HOURS FOR DAY> | ||
| 39 | 2022-01-05 | Client B | Project Y | Task 1 | Activity 1 | <TOTAL HOURS FOR DAY> | ||
| 40 | 2022-01-05 | Client B | Project Y | Task 5 | Activity 2 | <TOTAL HOURS FOR DAY> | ||
| 41 | 2022-01-05 | Client B | Project Z | Task 4 | Activity 2 | <TOTAL HOURS FOR DAY> | ||
| 42 | 2022-01-05 | Client C | Project Y | Task 1 | Activity 1 | <TOTAL HOURS FOR DAY> | ||
| 43 | 2022-01-05 | Client C | Project Z | Task 4 | Activity 2 | <TOTAL HOURS FOR DAY> | ||
| 44 | 2022-01-05 | Client D | Project X | Task 1 | Activity 3 | <TOTAL HOURS FOR DAY> | ||
| 45 | 2022-01-05 | Client D | Project Y | Task 1 | Activity 2 | <TOTAL HOURS FOR DAY> | ||
| 46 | 2022-01-05 | Client D | Project Y | Task 5 | Activity 3 | <TOTAL HOURS FOR DAY> | ||
| 47 | 2022-01-05 | Client D | Project Z | Task 3 | Activity 2 | <TOTAL HOURS FOR DAY> | ||
| 48 | 2022-01-05 | Client D | Project Z | Task 4 | Activity 2 | <TOTAL HOURS FOR DAY> | ||
Data | ||||||||
