How to Make a Time Card in Excel (Step by Step)

You do not need payroll software to make a time card in excel. A blank workbook, five disciplined columns, correct Time formats, and one daily formula will beat another abandoned time-clock login — if you build the calculator in the right order.

For selection and biweekly context, see Time card calculator excel.

Key takeaway: Build headers Date → Time In → Time Out → Lunch → Total Hours, format In/Out as Time, use =(Out-In-Lunch)*24 for decimal hours, drag down, then SUM the period — and guard blank rows with IF so unused days stay empty.

Before You Type Headers

Decide: weekly (7 days) or biweekly (14). Decide lunch style: single Lunch hours column vs lunch-out/lunch-in. Decide 12-hour vs 24-hour entry and stick to it. Gather last period’s paper card if you are migrating mid-cycle.

Set Up the Columns

Create a DIY time card calculator

Open a new workbook and bold row 1: Date | Time In | Time Out | Lunch (hrs) | Total Hours | Notes.
Format Date as date; Time In/Out as Time (1:30 PM or 13:30); Lunch as Number; Total Hours as Number with 2 decimals.
Freeze the header row.
Row 2 sample — enter a real day: 8:00 AM, 5:00 PM, Lunch 0.5.
Total Hours formula — if In=B, Out=C, Lunch=D: =(C2-B2-D2)*24.
Drag the formula down 7 or 14 rows.
Period SUM under Total Hours: =SUM(E2:E15).
Optional blank guard — =IF(A2="","",(C2-B2-D2)*24) so empty dates show blank.

This mirrors patterns taught in Microsoft’s timesheet FAQ and public tutorials: subtract times, multiply by 24 for decimal hours. Videos and QuickBooks how-tos emphasize formatting first — skip that step and the card fails silently.

Daily Entry Habits

Enter punches the day they happen when possible.
Never overwrite Total Hours with typed numbers — fix In/Out/Lunch instead.
Use Notes for mid-shift corrections (“left for appointment 2–3pm unpaid”).
Clear filters before trusting the period SUM.

Warning: If Total Hours is still formatted as Time, 7.5 may display as a clock. Switch to Number or General after the *24 formula.

When DIY Stops Being Fun

If you spend more time repairing formats than recording punches — or you need ready biweekly / hmm / CA variants — switch to a structured calculator workbook. The Biweekly Time Card Calculator Excel (PlanoNest — disclosure: we sell it) is a one-time-purchase file with Start Here onboarding. Live pricing sits on the product page.

Quick Build Checklist

Step	Done?
Time columns formatted as Time	☐
Lunch deducted in formula	☐
`*24` for decimals	☐
Period SUM present	☐
Blank-row IF (optional)	☐
Print area fits one page	☐

Frequently Asked Questions

Does Excel have a time card template? Yes — Microsoft’s gallery and many free downloads. Verify lunch and period length before use.

What is the formula for timecard in Excel? Commonly =(End-Start-Lunch)*24 for decimal hours. Overtime often uses =IF(hours>8,hours-8,0) after daily hours exist.

How do I create my own timesheet? Same skeleton as a time card calculator; add project or code columns only if you truly need them — they are optional for punch math.

How to convert 1 hour 30 minutes into hours? 1.5 decimal hours, or enter 1:30 as a Time duration and multiply by 24 when needed.

Operating Notes for Accurate Cards

Enter times in a consistent format for the whole period — either 12-hour with AM/PM or 24-hour military — and lock the column format before the first punch. If someone types 830 instead of 8:30, Excel may store a number that looks fine until the daily total jumps by hundreds of hours. Spot-check the first day after you paste from paper cards.

Treat unpaid lunch as a first-class input. A card that only has Time In and Time Out silently overpays when meal periods are unpaid. Prefer a Lunch (hours) column or explicit lunch-out / lunch-in pairs. When a day has no meal break, leave the lunch field blank or zero — never hide the rule in a comment.

Before you send totals to payroll, reconcile one sample week against a known-good calculator or manual math. Confirm overnight shifts (out earlier than in) use a midnight-crossing adjustment. Keep paper or PDF copies of signed cards when your process still requires wet signatures; the spreadsheet is the calculator, not always the legal archive.

Final Review Pass

Open Print Preview with a realistic biweekly card filled in. Confirm Time columns still look like times, Total Hours show decimals (or [h]:mm if you chose duration format), and period totals are not clipped off the page. Prefer PDF for the signed handoff; keep the .xlsx as the editable master. Never paste a fixed PlanoNest product price into the sheet header — live pricing stays on the product page.

Recordkeeping Habits That Prevent Payroll Surprises

Number each pay period clearly (for example 2026-W27–W28) and never reuse a period label after you lock totals. When a punch is corrected, note the reason in a Comment or Notes column instead of silently overwriting history. Store only what payroll needs — employee name, dates, in/out, breaks, totals — not unrelated HR fields that belong on a separate roster template. These habits matter more than fancy borders when a total is questioned later.

Formatting Details That Prevent Silent Failures

After you write =(C2-B2-D2)*24, click the Total Hours cell and confirm Number format with two decimals. Then test edge cases: a day with zero lunch, a day with a one-hour meal, and a day left completely blank. Blank should stay blank if you used the IF guard; zero-lunch should still compute elapsed time. If blank rows echo the previous day’s hours, your guard is missing or the Date column still contains leftover values.

Use Data Validation on Time In/Out if your Excel version makes it easy — restrict to time values or at least reject negative numbers. For Lunch, validation between 0 and 2 (hours) stops someone from typing 30 meaning thirty minutes when the column expects 0.5. Put that rule in a Start Here note so every new hire learns the convention once.

Print the blank card once before go-live. Confirm the period title, column headers, and total row fit on one page at 100% zoom. If Notes wraps into a second page, narrow the column or move long comments to a second sheet. A calculator that prints cleanly gets used; one that clips totals gets abandoned for sticky notes.