The quickest way to get an Excel time sheet is to use a built-in template: open Excel, go to File > New, type time sheet in the search box, and choose from options like “Weekly time sheet” or “Time sheet with breaks.” If you need something custom, you can build a simple weekly tracker in about five minutes using basic formulas for total hours and overtime. Below is everything you need to know about finding, customizing, and troubleshooting time sheet templates in Excel.
Why Use a Template Instead of Starting from Scratch
A template saves you from building column headers, formatting cells, and writing formulas. Most built-in Excel templates already include columns for employee name, date, task, start time, end time, and total hours. They also come with pre-set formulas that calculate daily and weekly totals automatically. If you use a template, you avoid common mistakes like forgetting to subtract lunch breaks or misaligning date ranges. For a small business or a freelancer tracking billable hours, a template is usually faster and more reliable than a blank workbook.
How the Built-In Excel Time Sheet Works
The built-in “Weekly time sheet” template (available in Excel 365, 2021, and 2019) has a table with rows for each day of the week and columns for time in, time out, and total hours. The total column uses a formula like =IF((D5-C5)+(F5-E5)>0, (D5-C5)+(F5-E5), 0) to sum morning and afternoon shifts, ignoring negative values if times are missing. To use it, simply type your start and end times in the correct cells, and the daily total appears automatically. The weekly total at the bottom sums the daily totals. On Excel for Mac, the same template exists under File > New, and the formulas work identically.
How to Build a Simple Time Sheet from Scratch
If you prefer to create your own, here is a straightforward layout that works in Excel for Windows and Mac:
- In row 1, put headers: Date, Start, End, Break (hours), Total Hours.
- Enter dates in column A (e.g., 1/6/2025 in A2).
- Enter times as actual time values (e.g., 9:00 AM in B2, 5:00 PM in C2).
- In E2, type the formula
=IF(OR(B2="",C2=""), "", (C2-B2)*24-D2). This subtracts the break and converts the time difference to decimal hours. - Copy the formula down for each row. For a weekly total, in E8 type
=SUM(E2:E7).
This layout gives you decimal hours (like 8.0) which are easier to multiply by an hourly rate. If you want to display hours as “8:00” instead, omit the *24 and format the cell as [h]:mm.
Choosing the Right Template for Your Needs
Not all time sheets are the same. Consider what you track:
- Weekly vs. bi-weekly vs. monthly: Weekly is common for hourly employees; monthly suits salaried staff or project billing.
- With or without breaks: If your team takes unpaid lunches, choose a template with a break column. If not, a simple start/end template is cleaner.
- Project or task breakdown: Freelancers often need a column for client or project name, plus a rate column to calculate invoice totals.
- Overtime tracking: Some templates include a separate overtime column with a formula like
=IF(E2>8, E2-8, 0)for daily overtime.
For a more advanced tracker, you can use the TIME function to handle shifts that cross midnight, but for most day-shift work the simple subtraction formula works fine.
Common Problems and How to Fix Them
One frequent issue is that total hours show as “0:00” or a negative number. This usually happens because the end time is earlier than the start time (e.g., a night shift starting at 11 PM and ending at 7 AM). The simple formula =C2-B2 returns a negative number. To handle overnight shifts, use =IF(C2<B2, (C2+1)-B2, C2-B2) which adds a full day when the end time is earlier. Another common problem is that the total cell shows a date or a time like “12:00 AM” instead of a number. That means the cell is formatted as a time; right-click the cell, choose Format Cells, and select Number with two decimal places. Finally, if you copy a template from a colleague and the formulas show #VALUE!, check that all time cells are actually formatted as time and not text. Select the column, go to Home > Number Format > Time.
Comparing Excel Templates to Google Sheets and Paid Tools
Excel’s built-in templates are free and offline, but Google Sheets offers similar templates online with real-time collaboration. If your team works remotely, Google Sheets might be easier because multiple people can edit simultaneously. However, Excel has more powerful calculation features and works better for complex payroll formulas. Paid tools like Toggl or Harvest automate time tracking with a timer and produce reports, but they cost money per user. For a small business with a few employees, an Excel template is often sufficient and costs nothing extra. For larger teams, a dedicated app reduces manual entry errors.
Costs and Safety Considerations
Excel templates are free if you have a Microsoft 365 subscription or a one-time purchase of Excel. There are also free third-party templates on sites like Vertex42, but be cautious: download only from reputable sites to avoid macros that could contain malware. If a template has a .xlsm extension (macro-enabled), it may contain VBA code. Before enabling macros, save a copy of the file, and only enable macros if you trust the source. Macros cannot be undone with Ctrl+Z, so always keep a backup. Also, never enter sensitive employee data into a template you downloaded from an unknown source; open it first, inspect the formulas, and then add your data.
Frequently Asked Questions
How do I make Excel calculate total hours automatically?
Use a formula that subtracts start from end time and multiplies by 24 to get decimal hours. For example, with start in B2 and end in C2, enter =(C2-B2)*24 and format the cell as a number. If you have breaks, subtract the break hours in the same formula.
Can I track overtime with an Excel time sheet?
Yes. Add a column labeled Overtime and use a formula like =IF(E2>8, E2-8, 0) where E2 is the total daily hours. This assumes a standard 8-hour day. For weekly overtime over 40 hours, use =IF(SUM(E2:E6)>40, SUM(E2:E6)-40, 0).
Why does my time sheet show a time like 12:00 AM instead of 8?
That means the cell is formatted as a time, not a number. Select the cell, press Ctrl+1 (Command+1 on Mac), go to the Number tab, and choose Number with 2 decimal places.
How do I handle shifts that cross midnight?
Use the formula =IF(C2<B2, (C2+1)-B2, C2-B2) where B2 is start and C2 is end. The +1 adds a full day, so 11 PM to 7 AM becomes 8 hours.
Is there a way to lock the formulas so employees don’t break them?
Yes. Select the cells with formulas, press Ctrl+1, go to Protection, and check “Locked.” Then go to Review > Protect Sheet and set a password. This prevents editing of formula cells while allowing data entry in other cells.
Related
- Excel Power Query Ungroup: How to Split Grouped Rows
- Excel Power Query URL: How to Import Data from a Web Page
- Excel Power Query Transpose Data: Steps & Tips
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Microsoft Word Bullet Hanging Indent: Set It CorrectlyIn Microsoft Word, select the bulleted paragraphs, then go to Home > Paragraph dialog launcher > Indents and…
- Google Sheets VLOOKUP: How to Find and Return DataGoogle Sheets VLOOKUP searches the first column of a range for a value and returns a value from…
- Excel Workbook Structure: Sheets, Cells, and FilesAn Excel workbook is the complete spreadsheet file: it contains one or more sheets, and each worksheet organizes…
- Excel and Google Sheets Templates: Uses and ChoicesExcel and Google Sheets templates are prebuilt spreadsheets you can copy and adapt for tasks such as budgeting,…
- Excel Indent Shortcut: Increase or Decrease Cell IndentIn Excel for Microsoft 365 on Windows, select the cell or cells and press Alt, H, 6 to…
- Archive All Emails in Outlook: Bulk Methods and LimitsTo archive a large number of Outlook emails, select messages in a folder or search results and choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.