Timesheet Calculator Excel Guide

Use columns A through F for Date, Clock In, Clock Out, Unpaid Break (min), Hours (decimal) and Hours (h:mm). Keep one continuous working period per row. Enter genuine Excel time values in columns B and C, and whole unpaid minutes in D. The downloadable CSV starter contains column headings and examples; it is not an Excel workbook with formulas already installed. Paste the formulas below into the indicated cells after opening it. Keep the original records separate from this checking worksheet. A copied value is easier to audit when its source date and break treatment remain visible beside it.

For the simple case of a period shorter than 24 hours, enter =IF(OR(B2="",C2=""),"",MAX(0,MOD(C2-B2,1)*24-D2/60)) in E2. MOD wraps a negative time difference across one day, and multiplying by 24 converts Excel’s fraction of a day to decimal hours. The blank guard prevents an unfinished period from being treated as complete. Paid breaks are not included in the unpaid-minutes column. The formula assumes ordinary wall-clock time, not elapsed time across a daylight-saving change or a shift lasting multiple full days.

For F2, use =IF(E2="","",E2/24) and apply the custom number format [h]:mm. The square brackets preserve total hours above 24 when you later sum durations. Format E as Number rather than Time; 7.5 is a decimal duration, while 7:30 is a clock-style display of the same length. If a regional Excel installation uses different formula separators or translated function names, adapt the syntax in Excel. Do not turn a numeric hours column into text simply to make its appearance match a printed card.

With 09:00 and 17:00 plus 30 unpaid minutes, E should contain 7.5 and F should show 7:30. With 22:00 and 06:00 plus zero unpaid minutes, the result should be 8. With separate 09:03–12:07 and 12:52–18:04 rows and no additional deduction, the total should be 8:16, or about 8.27 decimal hours. The lunch gap is already outside those rows. Deducting another 45 minutes would instead give 7:31. Use these checks before copying the formula down a full sheet.

If E2:E8 contains the first workweek, =MAX(0,SUM(E2:E8)-40) gives a simple weekly overtime estimate. Calculate the next week independently; do not sum two weeks and apply one 80-hour threshold. For a spreadsheet with several rows per day, choose the correct ranges or group by dates before applying a weekly rule. These formulas are arithmetic examples, not state-law, tax or compliance advice. They do not implement exemptions, double-time rules, different rates or an employer’s rounding of individual clock punches.

The site’s calculator CSV contains eleven columns, which differ from the six-column starter in this guide. Do not paste these example formulas into the export without first matching its column positions. CSV preserves cell values, not Excel formatting, multiple sheets or a completed workbook’s formula setup. Save an .xlsx copy when you want to retain your formulas and formats. Before sharing, remove unnecessary employee details and check the destination. Microsoft’s MOD function documentation explains the remainder operation used in the overnight example.