Excel Timesheet Template Generator

About this template

A timesheet is a record of hours that someone else has to trust: a manager approving it, a client being billed from it, a payroll run reading it. This builds a weekly one where the arithmetic is done by the sheet rather than by the person filling it in - type when you started, when you finished and how long you took for lunch, and the hours, the overtime split and the pay follow.

It suits hourly employees, contractors billing by the hour, small employers who run payroll from a spreadsheet, and anyone keeping a record of hours for a project. The rate, the overtime threshold and the multiplier are all cells at the top of the sheet, so a change to any of them re-prices the whole timesheet.

The sheet opens with the employee name, the week start date, the hourly rate, the overtime threshold in hours per week and the overtime multiplier in rows 3 to 7. Row 9 is the header: Day, Date, Start, End, Break (hours), Hours. Rows 10 to 16 are Monday to Sunday. Below them, row 17 is Total hours, rows 18 and 19 split that into regular and overtime hours, and rows 20 to 22 give regular pay, overtime pay and total pay.

Start and End are entered as times - 9:00, 17:30 - and Break as a decimal number of hours, 0.5 for half an hour. The Hours cell subtracts the times, converts the result from a fraction of a day into hours and takes the break off. A day with no start or end shows 0 hours rather than an error, so an unfilled day does not disturb the week.

How to use it

1. Set the rate and the overtime rule on this page - The hourly rate, the number of hours per week after which overtime applies (40 is the common default) and the overtime multiplier (1.5 for time and a half). All three land in cells at the top of the sheet and can be changed there later.
2. Enter the week start date - As yyyy-mm-dd. The Monday row takes it directly and each row below adds a day, so a whole week's dates come from one cell. Choose the day your working week starts on; the day names down the left are labels and can be retyped.
3. Type start, end and break for each day worked - Times as 9:00 or 17:30 - Excel reads either. Break in hours as a decimal: 0.5, 1, 0.75. Leave a day off blank and its Hours cell stays at zero.
4. Read the totals - Total hours sums the week. Regular hours is the total capped at the overtime threshold and Overtime hours is whatever exceeds it. Regular pay is regular hours times the rate; overtime pay is overtime hours times the rate times the multiplier; Total pay adds them.
5. Use more than one week - Choose up to four weeks on this page and the sheet repeats the block for each, with the following week's start date seven days on, and adds a grand total of hours and pay at the bottom.
6. Submit it - Print to PDF for a signed copy, or keep the workbook and add a sheet per month. Because the pay cells are formulas, an approver can check the arithmetic by clicking a cell rather than recomputing it.

Formulas in this workbook

Every formula below is written into the workbook itself, exactly as shown. The cell references are the ones the default options produce; change the row or column counts and they shift to match.

• =B10+1 - The Date column from Tuesday down: the day above plus one. Monday's date cell points at the week start date at the top, so changing that one cell re-dates the whole week.
• =IF(OR(C10="",D10=""),0,ROUND((D10-C10)*24-E10,2)) - Hours on the Monday row. Excel stores a time as a fraction of a day, so end minus start times 24 is the elapsed hours; the break is subtracted and the result rounded to two decimals. The IF and OR return 0 when either time is missing, so a day off does not produce an error.
• =SUM(F10:F16) - Total hours in row 17: the seven daily Hours cells added together.
• =MIN(F17,$B$6) - Regular hours in row 18: the total, capped at the overtime threshold in B6. A 44-hour week with a 40-hour threshold gives 40 here.
• =MAX(0,F17-$B$6) - Overtime hours in row 19: total hours minus the threshold, floored at zero so a short week shows no negative overtime.
• =F18*$B$5 - Regular pay in row 20: regular hours times the hourly rate in B5.
• =F19*$B$5*$B$7 - Overtime pay in row 21: overtime hours times the rate times the overtime multiplier in B7.
• =F20+F21 - Total pay in row 22: regular pay plus overtime pay.

Customising it

• Apply daily overtime instead of weekly by changing the Hours formula to cap at 8 and putting the excess in a new Overtime column: =MIN(F10,8) and =MAX(0,F10-8), then summing those columns.
• Handle an overnight shift by entering the end time as 24 hours plus the clock time, or by adding 1 to the end cell inside the formula: =ROUND((D10+1-C10)*24-E10,2) for a shift that finishes after midnight.
• Bill a client rather than pay an employee: rename the rate cell to your hourly rate, set the overtime multiplier to 1, and the Total pay row becomes the invoice figure.
• Add a project or task column between Day and Date so each day's hours can be tagged, then use a pivot table over several weeks to total hours by project.
• Round to the nearest quarter hour, if that is your payroll rule, by wrapping the Hours formula: =MROUND(existing formula,0.25).