Monthly timesheet template in Excel: office attendance sheet and common mistakes

How to build a monthly timesheet in Excel for office staff: column layout, total formulas, a daily timesheet and the mistakes that most often break it.

FUFerhat UğurluYazSis TeamPublished: November 19, 20268 min read
In short

A monthly timesheet in Excel has one row per employee, one column per day of the month and total columns at the end; each day gets a single code (normal, leave, sick, absent) and totals are calculated by formula. The most common error is a leave day showing up as an absence; it happens when the sheet does not match the leave records.

It is the last working day of the month, and the timesheet for the six-person accounting team is ready in Excel. The totals seem to add up, until an employee says, “My three days of leave aren’t on it.” You open the file: the leave form is in an email, those days were left blank on the sheet, and the total formula counts blank cells as absences. You fix it, and another row’s total shifts. Below you will find how to build a monthly timesheet for office staff in Excel, which formulas are safe and which mistakes come up most often.

A monthly timesheet is a table in which the work status of office staff for each day of the month (normal, on leave, on sick leave, absent, remote work and so on) is recorded with a single code, and which gives the total days and working hours at the end of the month.

How do you set up a monthly timesheet in Excel?

One row per employee, columns for the days of the month and total columns at the end are enough. The column logic can look like this:

Column groupContentTip
IdentityNumber, full name, departmentKeep each row fixed even if the order of employees changes; sort with a separate column.
DaysColumns from 1 to 28, 30 or 31Mark weekend and public holiday columns with a colour, but do not leave them blank.
CodesSingle letters such as N, L, S, A, RWrite the code list clearly in a corner of the file.
TotalsDays worked, leave, sick leave, absences, working hoursCalculate all of them by formula; never type them in.
ApprovalManager name, date, commentIf changes are made after closing, record the reason in this section.

Splitting the file into four sheets instead of keeping everything on one makes it easier to maintain:

  • Timesheet: employees’ daily codes for the month and the totals.
  • Codes: the meaning of each code in one place, for example N normal day, L annual leave, S sick leave, A absent, R remote work, O off-site duty.
  • Calendar: the month’s working days, weekends and public holidays; the control total is fed from this sheet.
  • Approval: who approved the period and on which date, and the reasons for any changes after closing.

Totals come from formulas: three safe formulas

Typing totals by hand is the most expensive habit in the file. The formulas below are written for English-language Excel; localised versions use their own function names, and depending on your regional settings the argument separator may be a semicolon instead of a comma. If you link each formula to the code in the header cell of its total column, the formula adapts when the code changes.

  • Days worked: =COUNTIF(D5:AH5,AK4). Assume N is written in cell AK4; the formula counts the N codes between D5 and AH5.
  • Leave days: =COUNTIF(D5:AH5,AL4). Enter the code L in cell AL4; use the same pattern for the sick leave and absence columns.
  • Working hours: =SUM(D6:AH6). If you enter working hours in a separate row below the code row, they will not mix with the codes.
  • Control total: Days worked, leave days, sick days and absences must add up to the number of working days in the month; if they do not, a day has been left blank or coded incorrectly.

What should a daily timesheet look like?

The monthly sheet is a summary; the daily sheet holds the raw record. In the daily sheet each row is one person’s day. The codes transferred to the monthly sheet at month end come from these rows. The fictional example below shows the smallest meaningful form of a daily record:

DateFull nameInOutNote
MondayEmployee A08:5718:02Normal day
MondayEmployee B09:3518:00Reason: external meeting, approved by manager
MondayEmployee CNo entryNo entryApproved annual leave

How do you keep a timesheet for office staff?

Office timekeeping is simpler than in a factory, but it has its own traps. Working hours are fixed, yet flexible start times, remote work and external meeting days blur the line between “late” and “absent”. These four rules keep the sheet simple:

  • If there is a flexible start window (for example a range in the morning), write it down once; arriving within the window should not count as late.
  • Define separate codes for remote work and off-site duty days; these days may have no clock-in record, but they are not absences.
  • If you deduct the lunch break from working time, write the rule down once and apply it to everyone in the same way.
  • Add manager-approved exceptions (meetings, doctor’s appointments) next to the sheet with a short reason.

The eight most common mistakes in an Excel timesheet

  1. Showing a leave day as an absence. Because the leave record does not match the sheet, the day stays blank and the formula counts the blank cell as an absence.
  2. Wrong total range. The month has 31 days, but the formula stops at the 30th column, so the last day appears in no total.
  3. Totals typed by hand. Someone corrects a cell, and the total stays at the old figure.
  4. Hidden rows or columns. The row of an employee who has left is hidden, but the total still includes it.
  5. Leaving public holiday and weekend columns blank. These days look “missing” and the control total does not add up.
  6. Multiple versions. Files like “timesheet_final.xlsx” and “timesheet_final_final.xlsx” circulate, and nobody knows which one went into the closing.
  7. Unlocked cells. Formula cells are accidentally deleted or overwritten with numbers.
  8. Silent corrections after closing. Once the period is closed, nobody can see who changed a cell or why.

If you want to match leave and timesheets in Excel, the method with the fewest errors is this: leave forms are entered into a table on a separate sheet (employee, start, end, type), and the leave codes on the timesheet are entered each month by checking this table. When a manager approves leave, the row must be added to the table the same day. This setup works, but it takes discipline: if the table falls behind by even one day, the sheet will be wrong again. The fact that the source of error is human forgetfulness shows where Excel reaches its limit; this is exactly where a system comes in.

A five-minute final check before sending the sheet to accounting catches most errors: does the control total add up in every row, are any cells left blank, are the days of everyone with a leave form coded, are the days of employees who left or started correct, and does the file name include the period and a version number? If the answer to any of the five questions is “no”, the file should not be sent.

When is Excel enough, and when is it not?

SituationExcelSystem
Fifteen to twenty people, one office, no shiftsUsually enough.Not required; can be added if the need arises.
Several offices or remote workFiles multiply and the merging workload grows.Collecting everything in one record makes the work easier.
Leave and clock-in/out kept in different placesMatching is done by hand; errors are inevitable.Leave records are matched with attendance records.
An audit trail is needed for corrections after closingCell change history remains limited.Approval and correction steps are recorded.

How does attendance matched with leave reduce errors?

Most of the eight mistakes above share the same root: records are kept in separate places and merged by hand at month end. The Time & Attendance Control module targets this root. It brings time-attendance (PDKS) records together on one screen and matches them to employees; it tracks late arrivals, early departures and absences separately as exceptions. Because attendance records are matched with leave records, leave days are kept apart from absences. Once exceptions are checked and approved, the attendance record is clear and the timesheet is produced from this clean data. On the overtime side, the Shift & Overtime Planning module records overtime requests and routes them through approval; you can see at any time which overtime has been requested and approved, and report on it.

If you started in Excel and have hit the limits of your sheet, you can move attendance and leave records into a single flow on KurumsalPortal while keeping the same coding logic. The easiest way to switch is to keep your current code list as it is and change only where the records are collected; employees keep the vocabulary they are used to, and HR no longer has to merge files at month end.

Frequently asked questions

How do you prepare a monthly timesheet template in Excel?

Create one row per employee, columns for the days of the month and total columns at the end. Each day gets a single-letter code (normal, leave, sick, absent); totals are calculated with formulas such as COUNTIF and SUM, and the control total is compared with the number of working days.

Which columns should an Excel timesheet include?

Full name, department, the days of the month, daily codes, totals for days worked, leave, sick leave, absences and working hours, plus manager approval and comment columns, are basically enough.

What is the difference between a daily and a monthly timesheet?

The daily timesheet holds the raw record for each day, such as clock-in, clock-out and notes. The monthly timesheet is the summary of these records, converted into codes and totalled.

How do you keep a timesheet for office staff?

Fixed or flexible working hours are defined once, separate codes are used for remote work and off-site duty days, manager-approved exceptions are recorded with a reason, and everything is matched with leave records.

Why does my leave day show up as an absence in Excel?

Because the leave record does not match the sheet, the day stays blank or without a clock-in record, and the formula counts it as an absence. Entering the leave on the sheet with a code, or keeping leave and attendance records in the same system, prevents this error.

FU
Ferhat Uğurlu · YazSis Team

Builds enterprise software, AI and digital transformation products together with the YazSis team.