How to Calculate Hours Worked in Excel (Step by Step)

Set up a sheet from scratch that turns clock-in and clock-out times into hours worked, with breaks and a weekly total.

Updated 2026-10-20

This walkthrough builds a working weekly timesheet in Excel in about five minutes. No macros, no add-ins.

Step 1: Lay out the columns

Type Monday to Sunday in A2:A8.

Step 2: Format the time columns

Select B2:C8, press Ctrl+1, choose Time, and pick 1:30 PM (or 13:30 for 24-hour). Now 9 followed by am is understood, and 17:30 works without AM/PM.

Step 3: The hours formula

In E2:

=IF(OR(B2="",C2=""),0,(C2-B2+(C2<B2))*24-D2/60)

Fill down to E8. Format E2:E8 as Number with 2 decimals. 9:00 AM to 5:00 PM with a 30-minute break shows 7.50.

Why it works: Excel stores times as fractions of a day, so C2-B2 is a fraction and *24 turns it into hours. (C2<B2) adds a day for overnight shifts. D2/60 turns break minutes into hours.

Step 4: Totals

Label A9:A11 Total, Regular and Overtime.

Step 5: Pay

Put the hourly rate in B13 and in E13 type:

=ROUND(E10*B13+E11*B13*1.5,2)

Step 6: Make it reusable

Save as a template (File > Save As > Excel Template). Each week, open the template, type the punches and save with the week’s date in the filename. Or skip the maintenance: the Time Card Calculator does the same calculation with overtime presets and exports to Excel.

Checking the result by hand

Take one day: in 8:15 AM, out 4:45 PM, 45-minute break. 4:45 PM is 16:45; 16:45 − 8:15 = 8:30, or 8.5 hours; minus 0.75 hours of break = 7.75 hours. If your cell shows 7.75, the formula is right.

Troubleshooting

• The cell shows #####: widen the column.
• The cell shows a time like 7:30 AM: the result is formatted as a time; change it to Number.
• A large negative number: the out time is before the in time and the (C2<B2) term is missing.
• 0.3125 instead of 7.5: you forgot *24.

Adding a second week and a summary

Copy A1:E13 to A16:E28 for week two, or better, duplicate the worksheet tab. Keep each week’s regular and overtime cells separate because overtime is owed per 7-day work week, never per pay period. A two-week total of 80 hours made of a 50-hour week and a 30-hour week still has 10 overtime hours. A summary cell such as =E13+'Week 2'!E13 adds the gross pay of both weeks.

Lunch as two punches

If employees clock out and in for lunch, use four time columns: in, lunch out, lunch in, out. The hours formula becomes:

=IF(OR(B2="",E2=""),0,((E2-B2+(E2<B2))-(D2-C2+(D2<C2)))*24)

This subtracts the exact lunch span rather than a fixed 30 minutes, which is what wage law requires when the lunch was shorter than policy.

Rounding to a payroll increment

To show hours to the nearest quarter: =MROUND(E2,0.25). To the nearest tenth: =ROUND(E2,1). Keep the unrounded column for the pay calculation if your policy only rounds for display; if you round for pay, remember that rounding is legally applied per punch, not per daily total, and the time clock should do it before the data reaches the sheet.

Daily overtime states

California, Alaska and Nevada pay time and a half after 8 hours in a day; Colorado after 12; California also pays double time after 12. Add columns F (Regular) and G (Daily OT): =MIN(E2,8) and =MAX(0,E2-8). Then the weekly test becomes =MAX(0,SUM(F2:F8)-40) so hours already counted as daily overtime are not counted again. Total overtime is SUM(G2:G8) plus that weekly figure.

Locking the sheet

Select the cells employees should type into (B2:D8 and B13), right-click, Format Cells, Protection, untick Locked. Then Review > Protect Sheet. Everything else, including the formulas, is now read-only without a password.