How to Build an Excel Timesheet with Lunch Breaks

A timesheet that records only time in and time out can’t tell a 30-minute lunch from a 90-minute one. Adding Lunch Out and Lunch In columns fixes that, and the formulas stay short. This guide builds a complete weekly sheet, shows a simpler break-minutes alternative, covers which breaks you may subtract, and adds the checks that catch bad punches. If you are new to time math in Excel, start with calculate hours worked in Excel.

The four-punch layout

Use one row per day, Monday in row 2 through Sunday in row 8:

Select B2:E8, press Ctrl+1 (Cmd+1 on a Mac), choose Custom and enter h:mm AM/PM. For F2:F8, choose Number with two decimal places, or Custom 0.00. In Google Sheets, use Format → Number → Custom number format for the same codes.

The daily hours formula

Hours worked are the full shift minus the lunch interval. In F2:

=((E2-B2)-(D2-C2))*24

E2−B2 is the time from clock-in to clock-out, D2−C2 is the lunch, and multiplying by 24 turns Excel’s fraction of a day into decimal hours. If a shift or a lunch can cross midnight, wrap each interval in MOD:

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

Here is a sample week calculated with the MOD version:

Tuesday’s 45-minute lunch turns a 9-hour span into 8.25 hours. Friday has no lunch punches; blank cells count as zero, so the formula returns the full 6-hour span.

Alternative: a break-minutes column

If your team records lunch as a length rather than two punches, use a shorter layout: B = Time In, C = Time Out, D = Break (minutes). Then:

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

A shift from 8:00 AM to 4:45 PM with a 45-minute break returns 8.75 − 0.75 = 8.00 hours. Dividing by 60 matters: typing 0.45 for 45 minutes, or subtracting 45 straight from decimal hours, gives the wrong answer. The minutes to decimal hours table lists every conversion.

Paid vs. unpaid breaks

Only unpaid time comes off a timesheet. Under the Fair Labor Standards Act (FLSA), the U.S. Department of Labor’s regulations separate two kinds of breaks:

• Rest breaks of about 5 to 20 minutes count as hours worked and must be paid (29 CFR 785.18). Don’t subtract a coffee break, even if the employee clocks out for it.
• Bona fide meal periods, ordinarily 30 minutes or more, can be unpaid if the employee is completely relieved of duty (29 CFR 785.19). A lunch eaten at the desk while answering phones is work time.

If employees sometimes use the lunch punches for a short break, this version deducts the interval only when it is at least 30 minutes, so short breaks stay paid:

=(MOD(E2-B2,1)-IF(ROUND(MOD(D2-C2,1)*1440,0)>=30,MOD(D2-C2,1),0))*24

MOD(D2−C2,1)×1440 is the break length in minutes. ROUND removes floating-point noise, so an exact 30-minute lunch isn’t misread as 29.9999 minutes. Change the 30 to match your written policy.

The FLSA does not require employers to provide meal or rest breaks, but several states do, and some (California, for example) require extra pay when breaks are missed. This is general information, not legal advice; state law may be stricter.

Handle missing lunch punches

Two blank lunch cells are fine; one is not. If someone records Lunch Out at 12:00 PM and forgets Lunch In, the lunch interval becomes 12 hours (or −12 without MOD), and the day is off by half a day. Check the punches with COUNT before calculating. In F2:

=IF(COUNT(B2:E2)=0,0,IF(OR(COUNT(B2,E2)<2,COUNT(C2:D2)=1),"Check punches",(MOD(E2-B2,1)-MOD(D2-C2,1))*24))

Reading it from the outside in:

• No punches at all returns 0, for a day off.
• A missing Time In or Time Out, or only one lunch punch, returns the text “Check punches”.
• Otherwise the formula returns hours as before. With both lunch cells blank, MOD(D2−C2,1) is 0, so a no-lunch day still works.

Because SUM ignores text, a flagged day drops out of the weekly total. Put a counter next to the total so it can’t be missed:

=COUNTIF(F2:F8,"Check*")

Weekly totals and the overtime hook

Below the daily rows, F9 holds the decimal total and F10 shows the same total as hours and minutes:

=SUM(F2:F8)
=F9/24

The sample week totals 40.75 hours. Format F10 as Custom [h]:mm so it displays 40:45; with plain h:mm it wraps past 24 hours and shows 16:45. In Google Sheets, use Format → Number → Duration or a custom [h]:mm format.

For FLSA overtime, split the decimal total at 40 hours and price it with the hourly rate in I1:

=MIN(F9,40)
=MAX(0,F9-40)
=ROUND(MIN(F9,40)*$I$1+MAX(0,F9-40)*$I$1*1.5,2)

At $20.00 an hour, the sample week is 40 regular hours, 0.75 overtime hours and $822.50 gross pay ($800.00 plus 0.75 × $30.00). Daily overtime rules, such as California’s, need a per-day calculation; see calculate overtime in Excel and FLSA overtime rules explained.

Ready-to-copy layout

For a two-week pay period, copy the block for week 2 and compute overtime separately for each week, because the FLSA does not allow averaging hours across two workweeks. The biweekly time card calculator handles that split for you, and the time card calculator is a quick way to check a single week.

Common errors and fixes

• 1:00 typed for 1:00 PM. Excel reads 1:00 as 1:00 AM. A lunch from 12:30 PM to “1:00” then looks negative, MOD turns it into a 12.5-hour lunch, and the non-MOD formula adds 11.5 hours instead. Always type AM or PM, or use 24-hour times such as 13:00.
• Lunch Out and Lunch In swapped. Same symptom. Column C must hold the earlier lunch time.
• ##### in a cell. The column is too narrow, or a time result is negative. Widen the column or use the MOD version.
• A total of 16:45 for a 40-hour week. The cell uses h:mm; change it to [h]:mm.
• The Hours column shows clock times. Format column F as 0.00, not as a time.
• Exact 30-minute lunches not deducted. Floating-point noise can make a break compute as 29.9999 minutes; keep the ROUND inside any minutes threshold.
• Text punches. Entries pasted from another system may be text. Test with =ISTEXT(B2) and convert with =TIMEVALUE(B2) or =--B2.