With both apologies and thanks in advance for assistance on this challenge:
I would like to understand an automatable approach to merging weekly time data from project time-tracking software (exported in CSV) into a consolidated monthly analytical sheet. By project/sub-project and date. As I am self-taught in Excel (and all things computer), background reading here and broadly gave me some ideas but I lack the real insights to successfuly work through - at least not without many days of bashing away. Hoping with the sample here I can short-cut to an approach (and understanding).
By way of background explanation: The purpose is a self-reporting to management and colleagues (I am allowed to use my own tool to time track in a way I like but I need to give them reporting on a monthly basis on man-days, etc). I previously used another tool for time tracking that gave me a convenient monthly table that didn't require any extra work, but it is end-of-life and not cloud based so my tracking only was on the single computer, and I couldn't easily go from home to work laptop in tracking. So moved to try this month a new cloud based time tracking app.
The core challenges are
- the new cloud tool I just started using only exports on a weekly basis rather than a monthly, so I have to collate weekly reports (CSV export) into monthly
- monthly collating is complicated as the sub-tasks / projects vary week to week (with new additions from time to time) so that the project listing output is not identical from week to week
- automating in part or whole to avoid stupidly spending hours on hand collating with the potential for manual error.
Excel version I am working with is Excel 2019 (Office Pro suite) - and at work Excel 2016 (pro suite also) [for precision although I think there are not meaningful differences between the two editions
Attached is sample sheet (from real but anonymised data, kept to 35 rows although real data set is 120 odd) where I have already combined the weekly exports into a single file by hand in a quick way, which ends up having different rows for different weeks, as what I did as simply copy over and use simple sort to organise by project name, so I end up with four rows for the same single month's work on a project in cases where I worked on such project/task every week. Which is messy and not ideal by far. The original CSVs are 7 day weeks.
A sample original single week export is in 2nd excel as example.
What I would ideally like to do is automate an approach to merge the month's worth of weekly exports, which would merge the CSV by
- Putting the date columns in date order (automatically rather than by hand as I have done now)
- Combine the rows that are the same project (same project name: this is standardised)
After reading in forum on data merging (and elsewhere) I suspect that Power Query would be an effective solution, but I was not able to succesfully work through with my limited understanding of the tool and the limitations of self-taught-by-doing command of Excel (and this apologies to forum experts who may ID an obvious solution already discussed)
Looking forward to any hand-holding and guidance.
Bookmarks