Hourly timesheet in Excel: hours worked, late arrivals and early leaves from check-in photos

Keep it simple, one row per shift:

• A: Date (a real date, e.g. 28/09/2026).
• B: Weekday, =TEXT(A2,"dddd"), so days off stand out.
• C: Time in and D: Time out, formatted hh:mm.
• E: Break minutes (a number, e.g. 60).
• F: Hours worked, as a decimal number you can add up.
• G: Late (minutes), H: Early leave (minutes).
• I: Note, e.g. "forgot to check out – confirmed with manager".

Never put in and out in one cell like "07:58–17:05": Excel cannot do arithmetic on text.

Excel stores a time as a fraction of a day, so 12 hours is 0.5. The formula for F2:

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

MOD handles a shift across midnight: in at 22:00, out at 06:00 still gives 8 hours instead of a negative number. Format column F as a Number with two decimals, not as a time, so you can add it up and multiply it by a rate.

If your system uses semicolons as the argument separator, swap the commas for semicolons. That comes from the region settings in Windows or macOS, not from the Excel version.

Put the shift start in a fixed cell, say $L$1 = 08:00, the end in $L$2 = 17:00, and the grace minutes in $L$3 = 5.

• Late (G2): =MAX(0,ROUND((C2-$L$1)*1440,0)-$L$3)
• Early leave (H2): =MAX(0,ROUND(($L$2-D2)*1440,0)-$L$3)

Multiplying by 1440 turns a fraction of a day into minutes. Whether the grace minutes are deducted is your company's rule; just agree on it and write it down. Handle night shifts separately, because the early-leave formula assumes the time out falls on the same day as the shift end.

Most timesheet arguments are not about formulas but about rules nobody said out loud:

• Which day does a night shift count on? The simplest answer: the day it started.
• Is lunch a fixed deduction or the actual break? If a shift ends at 12:30, is a full 60 minutes still deducted?
• A missing check-out photo counts as how many hours, who may fix it, and what note is required?
• Does the week start on Monday or Sunday?
• Rounding: to the minute, to 15 minutes, or not at all?

Write the answers at the top of the sheet or on a tab of their own. If the person doing payroll changes, the rules stay.

• Use the time printed on the photo, not the time the message arrived. A photo sent late because there was no signal is not a late arrival.
• Copy daily, not at the end of the month: nobody remembers a missed check-out from three weeks ago.
• Mark rows that were edited by hand, with the reason, so nobody wonders later.
• Show each person their rows before payroll closes. The worker usually spots an error faster than the bookkeeper.

Use =SUMIFS(F:F,A:A,">="&first_day,A:A,"<="&last_day) for the hours in a period, and =COUNTIFS(G:G,">0") for the number of late arrivals. If you pay by the hour, multiply the total by a rate kept in one fixed cell instead of typing the rate into every formula.