Timesheet template for Excel: free download and how it works
A timesheet in Excel needs three entries per day: start, end and break. Everything else, from standard hours on holidays to the overtime balance at the end of the month, the sheet works out for you. Download it free, then see how each formula works.
In short: one sheet per month, one row per day. Hours worked are =MOD(End-Start,1)-Break/1440 with the break in minutes, standard hours are 0 on weekends and holidays, and the difference =(Hours worked-Standard)*24 is in decimal hours. The total of that column is the overtime balance. Print it and sign it, and you have a paper time sheet.
Before you start: calculating with times (lesson calculate time), IF with OR, COUNTIF and absolute references.
Try it now
Breaks are quickest to type in minutes: 30 instead of 0:30. For Excel to subtract them from a time, the minutes have to become a fraction of a day.
Task In E2, calculate the hours worked: end minus start minus break. The break is in minutes, as a whole number in column D. The formula is filled down to E7.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | Start | End | Break (min) | Hours worked |
| 2 | Emma Walker | 8:00 | 16:30 | 30 | |
| 3 | Jacob Hayes | 7:30 | 16:00 | 45 | |
| 4 | Lucy Hughes | 9:00 | 18:00 | 60 | |
| 5 | Tom Turner | 7:00 | 13:00 | 0 | |
| 6 | Sara Kelly | 8:15 | 17:00 | 30 | |
| 7 | Adam Dixon | 8:30 | 15:30 | 20 |
Download the Excel timesheet template
The template holds one month with dates that fill in by themselves, standard hours per day, US or UK holidays, the difference in decimal hours, totals past 24 hours, a sample week, signature lines and an instructions sheet.
Enlarge previewDownload the finished template
The finished Excel file timesheet-template.xlsx, with sample rows and instructions, is free. All you need is a free account: no subscription, no payment details.
Create a free account and download the template Already have an account? Log in
How the timesheet is laid out
| Cell or column | Contents | Formula or input |
|---|---|---|
| B2 | First day of the month | Input, such as 1 Jan 2027 |
| B3 | Standard hours per workday | Input, such as 8:00 |
| H2 | Holidays | US, UK or None |
| A6, A7 to A36 | Date with weekday | =$B$2, then =IF(A6="","",IF(MONTH(A6+1)=MONTH($B$2),A6+1,"")) |
| B, C, D | Start, end, break in minutes | Inputs |
| E | Hours worked | =IF(OR(B6="",C6=""),"",MOD(C6-B6,1)-D6/1440) |
| F | Standard | =IF(OR(WEEKDAY(A6,2)>5,COUNTIF(Holidays,A6)>0),0,$B$3) |
| G | Difference in hours | =(N(E6)-F6)*24 |
| H | Note | Holiday name, otherwise free text |
Row 37 holds the totals and row 42 the signature lines. The Holidays sheet works out the holidays for the year in B2, including Easter and the Monday holidays.
Dates that fill themselves
You only enter the first of the month in B2. Each row below is the day before plus 1, as long as it is still the same month, so February stops after the 28th or 29th and the rest stays blank. The number format ddd d mmm shows the weekday as well, such as "Fri 1 Jan", which reads the same in the US and the UK. For the next month, copy the sheet (right-click the tab, Move or Copy, Create a copy) and change only B2.
Hours worked, even across midnight
End minus start is the time on site. For a shift from 22:00 to 6:00 that would be negative, so MOD wraps it: MOD(C6-B6,1) turns the overnight result positive. Then the break comes off, divided by 1440 because a day has 1440 minutes. The total uses the format [h]:mm, so Excel keeps counting past 24 hours instead of starting again at 0. The basics are in the lesson on calculating time in Excel.
Overtime in decimal hours
Hours worked minus the standard gives a time. When someone works less than planned, that time is negative, and Excel can only show it as ####. That is why the difference column uses decimal hours.
Task In F2, calculate the difference between hours worked and the standard hours in H2 as decimal hours, such as 0.5 for half an hour of overtime and -1 for an hour short. The formula is filled down to F7.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Name | Hours worked | Difference (h) | Standard per day | ||||
| 2 | Emma Walker | 8:30 | 8:00 | |||||
| 3 | Jacob Hayes | 8:00 | ||||||
| 4 | Lucy Hughes | 7:30 | ||||||
| 5 | Tom Turner | 9:00 | ||||||
| 6 | Sara Kelly | 7:00 | ||||||
| 7 | Adam Dixon | 8:45 |
This exercise is part of Premium.
Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.
See Premium Already Premium? Log in
The first exercise in this lesson is free.
The total in G37 is the balance for the month: positive means overtime, negative means hours short. Decimal hours are also what payroll needs, because 7:45 hours times an hourly rate only works as 7.75. The template uses N(E6) instead of E6, so days without entries count as 0 instead of causing an error. The dollar sign in the standard reference is explained in absolute references.
Standard hours with weekends and holidays
On weekends and holidays the standard is 0, otherwise it is the value in B3. Two conditions that lead to the same result go together with OR: WEEKDAY(A2,2)>5 spots Saturday and Sunday, because type 2 numbers Monday as 1 and Sunday as 7. COUNTIF looks the date up in the holiday list, and COUNTIF(list,A2)>0 means the date is a holiday.
Task In B2, calculate the standard hours for the day: 0 on weekends and on the holidays in E2:E3, otherwise the standard in E5. The formula is filled down to B8.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Date | Standard | Holidays | ||
| 2 | 5/3/2027 | 5/3/2027 | |||
| 3 | 5/4/2027 | 5/31/2027 | |||
| 4 | 5/5/2027 | Standard per workday | |||
| 5 | 5/6/2027 | 8:00 | |||
| 6 | 5/7/2027 | ||||
| 7 | 5/8/2027 | ||||
| 8 | 5/9/2027 |
This exercise is part of Premium.
Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.
See Premium Already Premium? Log in
The first exercise in this lesson is free.
In the template the list is the name Holidays, which points to a column on the Holidays sheet with only the dates for the region in H2: US federal holidays on their observed day, or bank holidays in England and Wales. A conditional formatting rule shades holidays light red and weekends grey, and the holiday name appears in the note column. Vacation and sick days are entered by hand: overwrite the standard with 0 and add a note. To plan time off for a team, use the PTO tracker.
Weekly, biweekly or monthly timesheet
The file covers one month. For a weekly timesheet, keep seven rows and delete the rest; for biweekly pay periods, keep fourteen. Set B2 to the first day of the period instead of the first of the month and remove the month check from column A, so that the dates simply run on with =A6+1.
- Printable: the sheet is set to landscape, and row 42 has lines for the date and both signatures.
- Several people: one file per person, or one sheet per person in the same file, with the name in E2.
- Locked formulas: unlock the input columns B to D and H under Format Cells > Protection, then use Review > Protect Sheet.
- Pay: add a column with
=E6*24*Ratefor daily pay, or multiply the total in E37 by 24 and the hourly rate.
Which records your employer needs, and how long they must be kept, depends on the country and the job. Check with your employer or HR; this page is not legal advice.
Common mistakes
Times typed with a period
8.30 is the number eight point three to Excel, not a time. Always type times with a colon. The template only accepts times in columns B and C.
Taking the break off twice
If the break is already 0:30 in the cell, do not divide it by 1440 as well. Pick one style; the template uses minutes.
The total jumps back to 0 after 24 hours
The format hh:mm only shows the time within a day. Totals need [h]:mm.
Timesheet formulas in German Excel
| English | German |
|---|---|
| MOD, IF, OR | REST, WENN, ODER |
| WEEKDAY, COUNTIF | WOCHENTAG, ZÄHLENWENN |
| MONTH, N | MONAT, N |
Which Excel versions support this?
Every formula and format in the template works in all current versions of Excel, including Excel for Mac and Excel for the web.
FAQ
Is this timesheet template free?
Yes, with a free account. No subscription and no payment details.
How do I track several months?
One sheet per month: copy the sheet, enter the next first of the month in B2 and clear the old times. For the year, add up the G37 totals of all sheets.
Does it handle night shifts?
Yes. MOD makes a shift from 22:00 to 6:00 count as 8 hours.
Can I use it as a weekly timesheet?
Yes. Keep seven rows, set B2 to the first day of the week and replace the formulas in A7 to A12 with =A6+1, so a week across two months still shows all seven days.
Does it work in Google Sheets?
The .xlsx file opens in Google Sheets, and MOD, WEEKDAY and COUNTIF exist there too. The template is built and tested for Excel, so check the formats after the import.
More files are on the Excel templates page.