Quote of the Day
When Winston is right he is unique. When he is wrong, Oh My God!
— Lord Birkenhead on Churchill
Figure 1: Old School Work Time Clock. (Source)
I am always amazed at how Excel gets used for everything in small companies. It is the glue that hold many small businesses together.
One common Excel task is tracking work hours. As a contractor, I encounter all sorts of approaches to recording work hours. One small company wants all of my hours captured in an Excel workbook that contains one worksheet per week. Every two weeks, an administrator goes in and captures the hours into another worksheet.
In addition to the company's desires, I want a couple of reports from this workbook.
- A list of my work hours by week and task.
- A list of my workdays, which I use to compute my mileage.
How to solve this problem in the simplest way possible? I have a few constraints and one personal request:
- I cannot confuse the administrator by adding extra worksheets.
- I cannot use macros.
- I cannot modify the worksheet structure:
- 3 lines for tasks assigned to each workday
- Monday is the start of the work week
- Each tab labeled by week number (Week1, Week2, ...)
- Because many of the customers and accounts repeat from one week to the next, I want to be able to add worksheets by copying the previous week's worksheet and incrementing the start of week date.
I took the following approach:
- I will hide my report worksheet.
- On my report worksheet, I will use an expanding spearing formula (aka 3D formula) and TEXTJOIN to create table with one column of cells with all the dates, tasks, and hours combined into delimited strings. To ensure that my table expands properly, I have added two hidden sheets called Begin and End that will bracket my weekly worksheets. I can include them in my TEXTJOIN address. They will ensure that as I add sheets, my TEXTJOIN formula will not need to be modified.
- I will use Power Query to process the table.
- I then generate two pivot tables with the information that I need.
- I added a pick list for all the Mondays in a year so that I cannot get a start date wrong.
This approach seems to be working. When I need my reports, all I do is unhide my report page, refresh my pivot tables, and I have the information that I need. When I start a new week, I just copy the previous week and change the start date.
My time card workbook is shown here. I have left everything unhidden so you can see what I did.