Annual timesheet template v2014

At the end of 2012 I turned a 4-weekly timesheet into an annual timesheet tracker. A number of issues popped up early on -- a really simple oversight on a late-requested addition -- which I had to provide a quick fix for. But I'm now in the process of improving it for 2014.

2013 features

• Protected sheets to prevent accidental messing up of formulas. (But not password protected because I trust that anyone intentionally editing it knows what they're doing. Do unto others...)
• Macros to unprotect / re-protect all sheets. (Excel 2010 doesn't seem to have a "protect all" option, so you otherwise have to lock all 13 sheets individually. Inefficient.) CTRL+SHIFT+U and CTRL+SHIFT+L.
• Summary page showing hours worked, hours accrued per month, and # of SDOs (scheduled days off, probably also known as time off in lieu days, rostered days off etc). This is to make it more useful as a tool for supervisors/managers.
• Graphs! (I don't know how useful they are, but it impresses some people -- important for adopting a new thing.)

2014 features

• Summary page now includes an automated table of all leave taken (and separate columns for different leave types) -- a request from supervisors to make it easier to see what they need to see at a glance.
• I did his through a pivot table drawing data from the 12 monthly tabs. I tried to find an index-type formula because it would have been able to draw more useful information, but couldn't work it out.
• But once you lock the data sheet you can't "Refresh" the pivot table data (which I don't really understand the point of). So created a button-operated macro to unlock the tab, refresh the pivot table, output the current date & time into a cell ("last refreshed..."), then re-lock the tab. Seems to work OK, and has the benefit of being more friendly to the end-user.
• Conditional formatting to highlight where someone accidentally inserts a space (" ") instead of leaving a cell blank, which threw out some of my formulas.
• Numbers re-formatted as numbers rather than hours.
• Excel doesn't do hours very well. It... treats them as time. Or days and hours. It's just really mind-boggling working with hours and minutes in Excel. The biggest issue is that Excel didn't like displaying negative time (-6:30), and would instead just show ######. Not especially useful for staff and supervisors wanting to calculate whether someone could take a day off an make up the hours another time.
• So the times have now been converted to number numbers, which allow negatives to show.

2015 update

• Fixed an error in the November tab. A few rows of formulas were missing from a hidden column. Now fixed and the column has been locked for editing.

Bugs

• Because these timesheets live on a network drive some of the security settings cause minor annoyances. E.g. every time you open it macros are disabled by default (why have a separate filetype for macro-enabled spreadsheets and then auto-disable them??). And you sometimes get a prompt to update the data or something. Haven't worked out how to avoid this, and probably shouldn't given the trade-off for more secure systems.
• From July-Dec there is an additional set of columns which calculate the time accrued from July-December. This was requested by our staff, because that was how we (used to) calculate accruals (i.e. in 6-month blocks). However, those extra calculations eat up a lot of RAM, I recommend completely closing Excel once you have finished with the timesheet so your computer can free up that memory. 

The little things

• Print settings pre-set. Margins, scaling, header/footer etc are all set in the template, so you don't have to fiddle with settings before pressing go.
• Hidden workings, highlighted entry fields. Formulas and workings and whatnot are hidden from view as much as possible. The bits you need to fill in are highlighted. It should all be easy on the eye and easy to use. This is a big step up from my early Excel escapades, where I'm pretty sure even I wouldn't have understood what I'd done a few months down the track.

What I'm proud of

• It works!
• Automated date creation based on the user identifying the correct year. Each tab starts on the first weekday of the month and finishes on the last weekday of the month, with other dates blacked out.
• A button the refresh the pivot table. This was a workaround but I'm really happy that I was forced to do it this way -- a button is so much nicer than what I had originally: "Right-click and select Refresh" to update, with a screenshot.

Final version updated November 2015: https://drive.google.com/file/d/0B3Ps-px3WIB9cHF2Q2ZLck5ESVU/view?usp=sharing -- Template copyright Peter Lead & The University of Sydney.

Instructions for use: https://drive.google.com/file/d/0B3Ps-px3WIB9VlUyMGdNa0dxVHc/edit?usp=sharing

Annual Timesheet Template by Peter Lead is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.
Contact: peter.lead@sydney.edu.au