Create a Dynamic Employee Timesheet in Excel with Auto-Calculated Overtime

You collect timesheets on Friday afternoon, and half of them are wrong. Someone forgot to subtract lunch, another person rounded to the nearest hour, and now you’re checking every row by hand with a calculator.

That hour or two of cleanup happens every single pay period. Worse, one small math error can turn into an overpayment or a wage complaint, and under the federal Fair Labor Standards Act (FLSA), overtime mistakes can get expensive fast.

Excel can do all of this for you. In this tutorial, you’ll build a timesheet that calculates daily hours, weekly totals, overtime, and gross pay from just clock-in and clock-out times. You’ll type each number once, and the formulas handle the rest.

The Scenario: Lone Star Landscaping in Austin, TX

Imagine you run a small landscaping crew in Austin with three employees. Each person clocks in and out daily, takes an unpaid 30-minute lunch, and earns time-and-a-half after 40 hours in a workweek.

Here’s the sample data for the week of 09/21/2026 through 09/25/2026. You’ll use it throughout this tutorial.

Employee	Date	Clock In	Clock Out	Unpaid Break (min)	Hourly Rate
Marcus Rivera	09/21/2026	7:00 AM	5:30 PM	30	$22.00
Marcus Rivera	09/22/2026	7:00 AM	5:45 PM	30	$22.00
Marcus Rivera	09/23/2026	7:00 AM	6:00 PM	30	$22.00
Marcus Rivera	09/24/2026	7:15 AM	5:30 PM	30	$22.00
Marcus Rivera	09/25/2026	7:00 AM	4:30 PM	30	$22.00
Dana Whitfield	09/21/2026	8:00 AM	4:30 PM	30	$18.50
Dana Whitfield	09/22/2026	8:00 AM	4:30 PM	30	$18.50
Dana Whitfield	09/23/2026	8:00 AM	4:00 PM	30	$18.50
Dana Whitfield	09/24/2026	8:00 AM	4:30 PM	30	$18.50
Dana Whitfield	09/25/2026	8:00 AM	3:30 PM	30	$18.50

Marcus works long days and will cross the 40-hour line. Dana stays under it. That contrast lets you test whether your overtime logic works in both situations.

Step 1: Set Up Your Columns and Enter the Data

Open a new workbook and type these headers in row 1, starting in cell A1:

Enter the sample data from the table above into columns A through E, then put each hourly rate in column G. Leave columns F, H, I, and J empty for now. Those are your formula columns.

[INSERT SCREENSHOT: Excel worksheet showing the ten column headers in row 1 and the sample data for Marcus Rivera and Dana Whitfield filled into columns A through E and column G, with columns F, H, I, and J still blank]

Format the Time and Date Cells

Excel stores times as fractions of a day, so formatting matters. Select the Date column, right-click, choose Format Cells, and pick Date with the 03/14/2012 style.

Next, select the Clock In and Clock Out columns. Open Format Cells again and choose Time with the 1:30 PM style. Format column G as Currency so rates display as $22.00.

[INSERT SCREENSHOT: The Format Cells dialog box open on the Number tab with the Time category selected and the 1:30 PM format highlighted]

Step 2: Calculate Daily Hours Worked

Click cell F2 and enter this formula:

=((D2-C2)*24)-(E2/60)

Press Enter, then double-click the small square in the bottom-right corner of the cell to fill the formula down the column.

How This Formula Works

Excel treats 12:00 PM as 0.5, because that’s half of a day. Subtracting Clock In from Clock Out (D2-C2) gives you the time worked as a fraction of a day.

Multiplying by 24 converts that fraction into regular hours. Then E2/60 turns the break minutes into hours, and the formula subtracts them. For Marcus on 09/21/2026, that’s 10.5 hours minus 0.5 hours, which equals 10 hours.

[INSERT SCREENSHOT: Cell F2 selected showing the formula =((D2-C2)*24)-(E2/60) in the formula bar, with the result 10 displayed in the cell]

If your shift ever crosses midnight, use this version instead:

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

The MOD function keeps the result positive when the clock-out time is technically “earlier” than the clock-in time.

Step 3: Build the Weekly Overtime Logic

Here’s where most people get stuck. Under the FLSA, overtime is based on total hours in a workweek, not hours in a single day. Marcus doesn’t earn overtime because one day ran long; he earns it because his weekly total passes 40.

So you need a weekly summary. Create a second block of cells to the right, starting in column L:

Cell	Label	Formula
L1	Employee	(type the name)
M1	Total Weekly Hours	`=SUMIFS($F$2:$F$11,$A$2:$A$11,L2)`
N1	Regular Hours	`=MIN(M2,40)`
O1	Overtime Hours	`=MAX(M2-40,0)`

Type “Marcus Rivera” in L2 and “Dana Whitfield” in L3, then copy the formulas in M2:O2 down to row 3.

How These Formulas Work

SUMIFS adds up every row in the Hours Worked column where the employee name matches. Think of it as asking Excel, “Add up hours, but only for Marcus.”

MIN(M2,40) caps regular hours at 40. MAX(M2-40,0) subtracts 40 from the total, and if the answer is negative, it returns 0 instead. That means Dana, who works under 40 hours, never shows negative overtime.

With the sample data, Marcus totals 49.5 hours, so he gets 40 regular hours and 9.5 overtime hours. Dana totals 37.5 hours, so she gets 37.5 regular hours and 0 overtime hours.

[INSERT SCREENSHOT: The weekly summary block in columns L through O showing Marcus Rivera with 49.5 total hours, 40 regular hours, and 9.5 overtime hours, and Dana Whitfield with 37.5 total hours, 37.5 regular hours, and 0 overtime hours]

Step 4: Calculate Gross Pay With Overtime

Now put dollars on those hours. Add these headers in P1, and then enter the formula in P2:

Hourly Rate in P1 and Gross Pay in Q1.

In P2, type Marcus’s rate of 22, and in P3 type Dana’s rate of 18.5. Then, in Q2, enter:

=(N2*P2)+(O2*P2*1.5)

Copy it down to Q3.

How the Pay Formula Works

The first half, N2*P2, multiplies regular hours by the normal hourly rate. The second half, O2*P2*1.5, multiplies overtime hours by the rate and then by 1.5 for time-and-a-half.

For Marcus, that’s (40 × $22.00) plus (9.5 × $22.00 × 1.5). The result is $880.00 plus $313.50, for a gross pay of $1,193.50. Dana’s is simply 37.5 × $18.50, or $693.75.

Format column Q as Currency so the totals display with dollar signs and two decimal places.

[INSERT SCREENSHOT: Completed pay calculation showing Marcus Rivera’s gross pay of $1,193.50 and Dana Whitfield’s gross pay of $693.75 in Currency format, with the formula visible in the formula bar]

Step 5: Make the Timesheet Dynamic

A timesheet that only works for one week isn’t much use. Two small upgrades will make it reusable.

Add a Data Validation Dropdown for Employee Names

Select the Employee column (A2:A50), then click Data > Data Validation. Under Allow, choose List, and in the Source box type your employee names separated by commas.

This stops typos like “Marcus Rivera” versus “Marcus Rivara,” which would break your SUMIFS totals without any warning.

[INSERT SCREENSHOT: The Data Validation dialog with Allow set to List and the Source field showing the employee names, next to the Data tab in the Excel ribbon]

Turn the Range Into an Excel Table

Click any cell in your data and press Ctrl + T, then confirm with OK. Excel converts the range into a Table that expands automatically when you add new rows.

Once you do this, you can replace fixed ranges like $F$2:$F$11 with table references such as Timesheet[Hours Worked]. Your formulas will then pick up new entries on their own.

Pro-Tip and Troubleshooting

Fix Hours Showing as Strange Times Like 10:00 AM

If your Hours Worked column shows a time instead of a number like 10, the cell has the wrong format. Select the column, open Home > Number Format dropdown, and choose Number. Set decimals to 2.

Fix #VALUE! Errors

This error almost always means a clock time was entered as plain text. Retype the time with a space before AM or PM (for example, 7:00 AM), or reformat the cell as Time and re-enter it.

Round to the Nearest Quarter Hour

Many employers round time to the nearest 15 minutes. Wrap your hours formula like this:

=MROUND(((D2-C2)*24)-(E2/60),0.25)

Check your state’s rules before you apply rounding, and keep it consistent so it doesn’t consistently shortchange employees. For payroll compliance questions, talk to your CPA or the U.S. Department of Labor.

Wrap-Up

You now have a timesheet that calculates daily hours, totals them by employee, splits regular and overtime hours, and produces gross pay. The formulas do the math, and you only enter clock times.

Save a blank copy of this workbook as a template, then duplicate it each pay period. Plug in your own employees and rates, and you’ll get your Friday afternoons back.