How to Create a Timesheet in Excel: A Step-by-Step Guide

Excel remains one of the most practical tools for building a timesheet — flexible enough to handle anything from a simple weekly log to a detailed project-based tracker. Whether you're tracking your own hours or managing a small team, understanding how the structure works gives you full control over the output.

What a Timesheet in Excel Actually Does

At its core, an Excel timesheet is a structured spreadsheet that records time in and time out, calculates hours worked, and optionally applies a pay rate to produce a total. The math Excel handles automatically — once formulas are set up correctly, you only need to enter your times.

The key functions involved are simple:

• Subtraction to calculate hours between two times
• SUM to total hours across days or weeks
• Multiplication to apply an hourly rate
• TEXT or number formatting to display time correctly

Getting the formatting right is where most people run into trouble, which is why it's worth understanding before building anything.

Setting Up Your Timesheet: The Foundation

Step 1: Label Your Columns

Open a new Excel workbook and start in row 1 with column headers. A basic weekly timesheet typically includes:

You can add columns for project codes, task descriptions, or overtime depending on your needs.

Step 2: Format Time Cells Correctly

This step is critical. Excel stores times as decimal fractions of a 24-hour day — so 6:00 AM is stored as 0.25, and 12:00 PM is 0.5. If your cells aren't formatted as Time, your entries won't calculate correctly.

Select your Time In and Time Out columns → right-click → Format Cells → choose Time → select a 12-hour or 24-hour format.

For the Total Hours column, format those cells as Number (with two decimal places) or use the [h]:mm custom format if you want hours displayed as hours and minutes rather than a decimal.

Step 3: Enter the Hours Formula

In your Total Hours cell (say, F2), enter:

=(D2-C2)*24-E2 

This subtracts Time In from Time Out, multiplies by 24 to convert Excel's decimal into hours, then subtracts any break time.

Important: If someone works a night shift that crosses midnight, this formula will return a negative number. In that case, use:

=MOD(D2-C2,1)*24-E2 

The MOD function handles overnight shifts by wrapping the calculation correctly.

Step 4: Build the Weekly Total

In the row below your last day entry, use:

=SUM(F2:F8) 

This totals all daily hours for the week. If you're tracking across multiple weeks in one sheet, add a subtotal row after each week.

Adding Pay Calculation 💰

If you want the sheet to calculate gross pay, add two more elements:

1. A dedicated cell for your hourly rate (e.g., cell I1, labeled "Hourly Rate")
2. A formula in a "Total Pay" cell:

=F9*$I$1 

The dollar signs lock the reference to your rate cell, so the formula stays accurate if you copy it elsewhere on the sheet.

For overtime, you'll need a conditional formula. A common approach for a 40-hour threshold:

=IF(F9>40, (40*$I$1)+((F9-40)*$I$1*1.5), F9*$I$1) 

This pays standard rate up to 40 hours and 1.5x beyond that.

Structuring for Different Timesheet Types

The setup above handles a basic weekly timesheet, but your needs might call for a different structure.

Weekly vs. Bi-Weekly vs. Monthly

• Weekly sheets are the simplest — 7 rows of data, one totals row.
• Bi-weekly sheets benefit from two weekly subtotals plus an overall total, keeping overtime calculations accurate per week.
• Monthly sheets work well as a single-tab layout with a row per day and weekly subtotals built in.

Project-Based Timesheets

If you're billing by project rather than by day, your columns shift. Instead of daily Time In/Out, you'd track project name, task, date, and hours logged manually. A SUM with a filter or a SUMIF formula lets you pull total hours per project:

=SUMIF(B:B,"ProjectName",F:F) 

Multi-Employee Timesheets

For teams, the most manageable approach is one tab per employee, with a summary tab that pulls totals using references like ='EmployeeName'!F9. This keeps individual data clean while giving you an overview in one place.

Common Mistakes That Break the Formulas

🔍 A few things consistently cause issues:

• Entering times as text (e.g., typing "9am" instead of "9:00 AM") — Excel won't recognize it as a time value
• Mixing 12-hour and 24-hour formats in the same column
• Forgetting to format the Total Hours column correctly — leading to results like "0.375" instead of "9"
• Not accounting for overnight shifts — the standard subtraction formula doesn't handle midnight crossings without MOD

Variables That Shape Which Approach Works Best

Once the fundamentals are in place, the "right" timesheet design depends on factors that vary significantly between users:

• How many people are involved — a solo freelancer needs a very different layout than a manager tracking a team
• Whether overtime rules apply — and at what threshold (40 hours federal standard, or different thresholds by state or contract)
• How often the sheet needs to be shared — if you're emailing it to a payroll processor, simplicity matters; if it stays internal, complexity is fine
• Your Excel version — some features like dynamic arrays or XLOOKUP work differently across Excel 2016, 2019, and Microsoft 365
• Whether the sheet integrates with other systems — payroll software, accounting tools, or project management platforms may require specific export formats

A freelancer billing three clients monthly has different formula needs than a small business owner running bi-weekly payroll for hourly employees. Both can use Excel effectively — but the structure that makes sense for one would add unnecessary complexity for the other.