Automated data processing, 30 seconds to completion
The monthly breakdown is the starting point for our data analysis, so this source data table we need to ensure that.
Data as error-free and accurate as possibleEasy to use, templated
The reason we correct the categorization and notes once a month is to maximize the accuracy of the data.
Secondly, I set up the "Week" and "Month" super tables, which will automatically update the pivot table just by pasting a data refresh. Then I set the "S*" identifier that needs to be parsed each month to VBA (batch replacement + disaggregation + deletion of the first entry at the beginning of the month) to speed up data processing.
These steps are all packaged in an Excel sheet with the macros turned on, guaranteeing no more than 5 minutes from adding new data to completing the visualization, preparing the data environment for subsequent analysis.
Finally, I set up a monthly public schedule (2019), refreshed the data and copied and pasted it into an image to send to my circle of friends to sync my time goal progress.
In other words, the complete event log data table contains "Week" data, "Month" data, "Week Insight" data, "Month Insight" data, as well as "Event Definition Table" and "Layout" memos, a data processing "VBA" table, and finally the "2019" goal progress table presented monthly.
It's not that we can make such data with a table, it's also necessary to bury the markup in TimeTrack.
For example, the "S*" tag and the "#, @," symbols in the comments column should be added to the activity when it is created.
With this specification source data table, we can process time data more quickly and easily.
The source data template was significantly updated in 2019, highlighting key indicators and improving visualization.
The illustration is as follows:
monthly report
With this report, from exporting your data in TimeTrack to importing it into Excel to start analyzing it, it takes 2 minutes.
Motion Picture Presentation
This set of templates took almost half a year from having ideas in prototype to actually implementing them on Excel.
From the template function processing to the VBA, layout to the color matching, to the red and green lights up and down arrow markers, and even row height column width, from large to small I can take into account the details are all added in one Excel!