How to use
- Pick the scenario that matches your sheet.
- Set the columns that hold time in, time out, break minutes and hours, and the first data row.
- Copy the Excel or Google Sheets formula into the first row and fill down.
Worked example: Lunch deduction in column E
With time in B2, time out C2 and break minutes in D2, the hours formula is =((C2-B2)*24)-(D2/60). For 8:00 AM to 4:30 PM with 30 minutes it returns 8.
For a night shift use =MOD(C2-B2,1)*24-(D2/60): 10:00 PM to 6:00 AM returns 8 instead of −16. Format B and C as time and E as a number with 2 decimals.
Frequently asked questions
Why multiply by 24?
Excel stores times as fractions of a day. 0.5 is 12 hours, so the difference × 24 gives hours.
Why does my result show as a time like 8:00?
The hours cell inherited a time format. Set it to Number with 2 decimals.
How do I round to 15 minutes?
Excel: =MROUND(B2,"0:15"). Google Sheets: =MROUND(B2,TIME(0,15,0)). Apply it to each punch, not the total.
How do I split overtime?
Put the weekly total in one cell, then =MIN(total,40) for regular and =MAX(total-40,0) for overtime.
Are the Google Sheets formulas different?
Only for MROUND with times; everything else is identical.
Can I download a sheet with these built in?
Yes, see the timesheet templates, each with working formulas.