Excel Time Card Calculator: Formulas, Setup, and Tips

Yes. In Excel for Microsoft 365 on Windows, a basic time card calculator can subtract clock-in from clock-out, deduct an unpaid break, and convert the result to decimal hours. Put start time in B2, end time in C2, and break duration in D2; in E2 enter =MOD(C2-B2,1)-D2. Format E2 as [h]:mm. The formula handles a shift that crosses midnight; for example, 8:00 PM to 4:30 AM with a 30-minute break returns 8:00, or eight hours worked.

What an Excel time card calculator does

A time card calculator turns recorded work times into durations that can be totaled for a day or a pay period. A simple worksheet can show the date, clock-in time, clock-out time, unpaid break, hours worked, and a weekly total. It can also display hours as decimals, which some payroll systems request.

Excel does not treat a time such as 8:00 AM as the number eight. It stores time as part of a 24-hour day: noon is one-half, and 6:00 AM is one-quarter. Formatting changes how a value looks, not the underlying value. This is why a duration can be added or converted to hours, but a plain subtraction may give an unexpected result for an overnight shift.

A workbook calculates from the entries you provide; it does not verify that the entries are accurate, determine whether a break is legally unpaid, or decide which hours qualify for overtime. Think of it as a calculation aid, not a substitute for an employer’s approved timekeeping and payroll process.

Set up the time card columns

Use one row for each work shift. In row 1, add headings such as Date, Start, End, Unpaid break, Worked duration, and Decimal hours. Enter the date in column A, start time in B, end time in C, break duration in D, and reserve E and F for calculated results. This arrangement keeps the original punches visible beside the calculations.

Enter a time with a colon, such as 7:45 AM or 19:45. Use a consistent 12-hour or 24-hour display so entries are easy to review.
Enter an unpaid 30-minute break as a real time value such as 0:30, not as the text “30 minutes.” The value must be a duration Excel can subtract.
Keep dates in their own column. A date helps identify each shift and makes it easier to sort or review a pay period.
Use a separate row for each shift rather than putting multiple shifts into one cell. If a person clocks out and returns later, record the intervals separately or use additional start/end columns with a clearly defined total.

To set a display format in desktop Excel for Windows, select the relevant cells, press Ctrl+1, choose the Number tab, and select Time or Custom. On a Mac, Command+1 opens Format Cells. Excel for the web has a Format Cells interface available from the Home tab; the exact dialog presentation can differ from desktop Excel. The stored time values and formulas work the same way.

Calculate worked time, including overnight shifts

With the layout above, enter this in E2, then fill it down for the other shifts: =MOD(C2-B2,1)-D2. The assumed layout is a start time in B2, an end time in C2, and an unpaid break duration in D2. If the shift runs from 9:00 AM to 5:15 PM and the break is 0:45, the result is 7:30, or seven hours and 30 minutes. If the shift runs from 8:00 PM to 4:30 AM with a 0:30 break, the result is 8:00.

MOD returns the remainder within a 24-hour day, so it prevents a negative result when the end time is earlier on the clock than the start time. That makes the formula useful when shifts cross midnight, provided the shift is less than 24 hours and the entries represent one continuous interval. A break longer than the recorded shift produces a negative duration; treat that as an input error to investigate, not as a valid result.

Format the duration cells as [h]:mm, rather than ordinary h:mm, when you will add multiple shifts. The square brackets tell Excel to show accumulated hours beyond 24. Without them, a total of 27 hours can display as 3:00, which is misleading even though the stored value may still be correct. For a single shift shorter than a day, ordinary time formatting can look acceptable, but using the elapsed-hours format consistently avoids confusion.

Convert durations to decimal hours

Payroll forms and reports often ask for hours in decimal form. A duration of 7 hours and 30 minutes is 7.5 hours, not 7.30 hours: the digits after the decimal represent a fraction of an hour, not minutes. With worked durations in E2:E8, put this in F2 and fill down: =E2*24. The assumed layout is the calculated duration in E2; a result of 7:30 returns 7.5. Format column F as Number with a suitable number of decimal places, rather than as a time.

For a specific check, 15 minutes equals 0.25 hour, 30 minutes equals 0.5 hour, and 45 minutes equals 0.75 hour. A shift of 8 hours and 15 minutes therefore converts to 8.25. Multiplying by 24 works because Excel stores a full day as 1 and a day contains 24 hours. Do not multiply a value that is already decimal hours; doing so would overstate it.

Keep the duration and decimal columns if both formats are useful. The duration is easier for a person to verify against a time card, while the decimal is convenient for systems that require decimal hours. Avoid rounding each shift unless a documented payroll rule requires it: rounding each row can produce a different period total than summing the unrounded values and rounding once.

Total a week or pay period

If decimal hours for seven daily rows are in F2:F8, enter =SUM(F2:F8) in F9. The assumed layout is one day per row, with decimal hours in F2 through F8; if the entries add up to 42.25, the weekly total is 42.25 hours. You can also total the duration column instead, but format that result as [h]:mm so totals above 24 hours remain visible.

For a longer pay period, extend the range to include all shift rows, or use a clearly labeled total row for each week. If the workbook includes multiple employees, do not sum everyone’s hours into one employee’s total. Separate employee records or use an appropriately designed table and filters so each person’s time can be checked independently.

Totals are easier to audit when the source punches remain unchanged and calculations sit in separate columns. Consider adding a notes column for corrections or unusual shifts, and retain the original time record according to the organization’s recordkeeping process. If a punch is edited, the calculated result changes automatically, so a note can help explain why a total differs from the original record.

Overtime calculations and important limits

Excel can compare a weekly total with a chosen threshold, but the correct threshold and treatment of hours depend on the applicable rules, workweek, worker classification, and employer policies. In the United States, a common federal standard for many nonexempt workers is overtime after 40 hours in a workweek, but exceptions and state or local requirements may apply. Other countries and arrangements have different rules. Confirm the applicable requirements with a qualified professional or the relevant authority rather than treating a spreadsheet example as legal advice.

For a purely illustrative calculation where a 40-hour threshold has already been confirmed, suppose the weekly decimal total is in G2. To show hours up to that threshold, enter =MIN(G2,40). The assumed layout is total weekly hours in G2; if G2 is 42, the result is 40. To show hours above the same threshold, enter =MAX(G2-40,0) in another cell. With G2 equal to 42, it returns 2. These formulas identify amounts only; they do not calculate an overtime pay rate or determine which hours legally count.

Do not assume that a daily total, a pay-period total, and a workweek total are interchangeable. Overtime rules may be based on a defined workweek, so a two-week total cannot necessarily be compared with a single combined threshold. Keep weekly boundaries explicit and have the policy reviewed before relying on an automated result.

Common spreadsheet errors and checks

A time card can look plausible while containing an error. Check the entries and display formats before using totals for payroll. Useful safeguards include comparing unusually long shifts with source records, checking that breaks do not exceed shifts, and reviewing blank or incomplete rows.

Overnight shifts show negative time: A formula using only end minus start does not account for midnight. Use the MOD formula above, or record full date-and-time values for each punch.
A total rolls over at 24 hours: Apply the custom duration format [h]:mm to accumulated time.
Decimal hours look like minutes: Convert a duration by multiplying by 24 and format the result as a number. 8:30 becomes 8.5, not 8.30.
A formula returns an unexpected value: Check whether the punches and break are real numeric time values rather than text, and check that the break is entered as a duration.
Blank rows affect reports: Keep incomplete records distinguishable from zero-hour shifts. Review blanks before totaling or exporting records.

For a shift with actual dates attached, record both the start date and time and the end date and time when possible. A time-only formula cannot distinguish a 4:30 AM end on the next day from one on the same day without an overnight assumption. Also note that a spreadsheet’s local clock times do not store time-zone or daylight-saving context. Shifts spanning a daylight-saving change may need to be checked against the employer’s authoritative timekeeping record.

When Excel is a good fit—and when it is not

Excel is a practical choice for a small, stable time card process when a person can enter and review the records, the rules are straightforward, and a spreadsheet is an accepted format. It is flexible: columns, totals, and print layouts can be adapted without requiring a dedicated timekeeping system. A basic calculator can also be created without buying a separate time-card template, although access to Excel itself depends on the user’s Microsoft licensing or other available version.

A shared workbook becomes harder to control when many people enter punches, managers approve edits, schedules change frequently, or records must connect directly to payroll. Dedicated time and attendance software may offer individual logins, approval histories, mobile clock-ins, schedule management, and exports. Those capabilities vary by product and may involve subscription costs; compare the actual features, retention policies, and payroll compatibility rather than assuming every system includes them.

For a one-off calculation, a calculator or a trusted timekeeping application may be quicker than building a workbook. For recurring use, a spreadsheet can be a useful transparent record, but protect it from accidental edits, limit access to sensitive employee information, and keep a separate backup. Avoid circulating payroll data in a broadly accessible file when a more controlled method is available.

Frequently Asked Questions

Can Excel calculate a shift that ends the next day?
Yes. The MOD formula handles a time-only shift that crosses midnight, such as 8:00 PM to 4:30 AM. Recording the actual date as well as the time is clearer when shifts are long, irregular, or audited.

How do I enter a 30-minute break?
Enter it as an Excel duration, such as 0:30, in the break column, then subtract that cell in the worked-time formula. Confirm whether the break should be deducted under the employer’s timekeeping rules; the spreadsheet cannot decide whether a break is unpaid.

Why does my total time show 3:00 instead of 27:00?
Excel’s ordinary clock format wraps after 24 hours. Format the total as [h]:mm to display cumulative hours, including totals above a day.

How do I convert hours and minutes to decimal hours?
Multiply the Excel duration by 24 and format the result as a number. For example, 7:30 becomes 7.5 hours because 30 minutes is half an hour.

Does an Excel time card calculator determine overtime pay?
No. It can total hours or compare them with a threshold you specify, but applicable overtime rules, workweek definitions, pay rates, and exceptions must be confirmed separately. Have payroll calculations checked against the rules that apply to the worker and location.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.