On the face of it, processing timesheets with Power Query ought to be fairly straightforward. By “processing timesheets” I am primarily thinking about records of hours worked that feed into payroll or job costing calculations.
Using the same technique as I set out here for multiple forecasting, we should be able to design a process whereby timesheets end up in the same folder. You can then combine them into a payroll input file, or a reporting dashboard, or whatever you need.
And here is an excellent piece by one of the gurus of Power Query, Ken Puls, on some of the common timesheeting issues that Power Query can help you with. For example, dealing with times in different formats, inserting lunch breaks, processing overnight shifts.
Microsoft 365 gives organisations further tools for helping to collect the data. For example, you could use Power Automate to make sure that timesheets emailed to “timesheets@yourorg.co.uk” were automatically saved into the relevant folder.
Yay, let’s automate it now!
However, despite all of these clever tricks there are still issues. Applying my process for assessing internal controls, and thinking about my experiences of organisations with timesheets, I can think of the following potential problems:
- what happens if someone submits two timesheets?
- does the process need to allow for authorisation and/or amendment?
- how do you know your data is complete?
- and the biggest of all; how do you ensure that people use the right personal identifier?
The fact that I have said “personal identifier” instead of “name” is deliberate. Names are a really unreliable way of consistently identifying the same person. For example, you may have two or more people with the same name. People may use abbreviations or otherwise enter their name inconsistently. Perhaps I enter my name as “Jess” sometimes and “Jessica” on other occasions.
There are various possible solutions. You could try to encourage use of a payroll number instead of name. (This is such an accountant’s solution! Do you know your payroll number in your current organisation?).
Or more likely, you could introduce some kind of app for collecting the data that is tied to a unique identifier, eg your organisational email.
Power Query can definitely speed up aspects of timesheet processing. But it’s really important to think through the entire process and current issues before leaping to any automated solution.
If you want a quarterly newsletter summarising latest developments in Excel and other things I've found interesting or useful, please sign up here. I won't use your data for anything else.