Just in time (almost) for a new year I finished my basic timesheet for 2012 and if you’re interested, you can download itĀ from here: timesheet-2012 (right-click and select “Save as…”).
What it contains:
-Months split into sheets, preceded by an overview sheet
-Adjustable settings for working hours per day
-All time fields are formatted to show a 24 hour clock.
-Weekends, holidays and days with reduced working hours are marked in different colors
-Swiss holidays resp. holidays for the city of Zurich (all in English)
-Some working days come with reduced working hours or half-days, these are included as well.
What it does:
-After you enter your starting time, finishing time and how much time you spent at lunch, the sheet will update the monthly total which in turn will update the total in the Overview sheet.
-Compares your daily efforts to the working hours per day and calculates a positive or negative overtime total, incl. red coloured negative overtime hours
What it does not do:
-Update itself for the next year. If you want the same timesheet for next year, you have to manually update the weekend color markings
-Adjust the holidays to different regions or countries. Frankly speaking, it’s all hard-coded š
-Allow for several entries per day e.g. if you work from 8am to 10am, from 1pm to 3pm and finally from 7pm to 9pm you will have to enter e.g. starting time 8am / finishing time 9pm / break 07:00 (3 hours + 4 hours)
-Convert your spreadsheet to a nicely formatted, printable output.
Notes:
If you’re unhappy with my timesheet, feel free to use it as a basis for your own (hopefully) improved version. Here are some learning steps I had to take:
-Calculating time difference in Calc (or any other spreadsheet software?) is a bitch. The crucial point is the cell formatting, not the cell formula. Most likely, you will have to customize an existing format. Have a look at the formatting of any cell in the “+/-” columns.
-Colored results are easy. Again, have a look at the formatting of any cell in the “+/-” columns.
-Copying a formula from one cell to anotherĀ will automatically adjust the referenced cells in the formula. You probably knew that and I knew it too. To prevent this from happening: precede a cell reference with a “$” e.g. “=F16” should be written as “=$F$16”. Maybe you knew that as well. BUT! Did you also knew that references are also shifted per sheet when you copy sheets?
If you happen to have two sheets (“Overview”, “January”) with a reference of “=Overview.C18” in the cell A3 in the “January” sheet and you copy the “January” sheet, rename it “February” and place it before the last sheet, the “=Overview.C18” reference will automatically be adjusted to “=January.C18”. Apparently, these references don’t refer to a sheet’s name by default (as expected?) but refer to the relative order in which the sheets are ordered. In order to fixate a reference across sheets, use the “$” again e.g. “=Overview.C18” should be “=$Overview.$C$18”
Last but not least, you can password-protect your sheet. Select “Save As…” and check the “Save With Password” checkbox at the bottom of the Save dialogue.
HTH some…