What is a timesheet for?
It proves and totals each employee’s working time to prepare payroll: normal hours, overtime, lateness, absences and leave. For a small team on a single site, a well-built Excel workbook is often enough.
In Morocco, legal working time is 44 hours a week in non-agricultural activities (2,288 hours a year, article 184 of the Labour Code), and hours beyond that are paid at a premium under article 201.
Which columns should an Excel timesheet have?
| Column | Content | Formula or entry (row 2) |
|---|---|---|
| A · Date | The day worked | Entry, format dd/mm/yyyy |
| B · Day | The day of the week, to spot rest days | =TEXT(A2,"dddd") |
| C · Employee ID | The employee’s identifier | Entry (the same as in payroll) |
| D · Name | Last and first name | Entry, or =VLOOKUP(C2,Employees!A:B,2,FALSE) |
| E · In | Arrival time | Entry hh:mm |
| F · Break start | Out for lunch | Entry hh:mm |
| G · Break end | Back from break | Entry hh:mm |
| H · Out | Departure time | Entry hh:mm |
| I · Hours worked | Presence minus the break | =MOD(H2-E2,1)-MOD(G2-F2,1), format [h]:mm |
| J · Scheduled hours | The day’s scheduled hours | Entry, for example 08:00 |
| K · Difference (hours) | Positive = extra hours, negative = shortfall | =(I2-J2)*24 |
| L · Status | P present, A absent, PL paid leave, S sick, H public holiday, R rest | Drop-down list (Data > Validation) |
| M · Note | Justified lateness, mission, forgotten punch… | Free entry |
A · Date
- Content
- The day worked
- Formula or entry (row 2)
- Entry, format dd/mm/yyyy
B · Day
- Content
- The day of the week, to spot rest days
- Formula or entry (row 2)
- =TEXT(A2,"dddd")
C · Employee ID
- Content
- The employee’s identifier
- Formula or entry (row 2)
- Entry (the same as in payroll)
D · Name
- Content
- Last and first name
- Formula or entry (row 2)
- Entry, or =VLOOKUP(C2,Employees!A:B,2,FALSE)
E · In
- Content
- Arrival time
- Formula or entry (row 2)
- Entry hh:mm
F · Break start
- Content
- Out for lunch
- Formula or entry (row 2)
- Entry hh:mm
G · Break end
- Content
- Back from break
- Formula or entry (row 2)
- Entry hh:mm
H · Out
- Content
- Departure time
- Formula or entry (row 2)
- Entry hh:mm
I · Hours worked
- Content
- Presence minus the break
- Formula or entry (row 2)
- =MOD(H2-E2,1)-MOD(G2-F2,1), format [h]:mm
J · Scheduled hours
- Content
- The day’s scheduled hours
- Formula or entry (row 2)
- Entry, for example 08:00
K · Difference (hours)
- Content
- Positive = extra hours, negative = shortfall
- Formula or entry (row 2)
- =(I2-J2)*24
L · Status
- Content
- P present, A absent, PL paid leave, S sick, H public holiday, R rest
- Formula or entry (row 2)
- Drop-down list (Data > Validation)
M · Note
- Content
- Justified lateness, mission, forgotten punch…
- Formula or entry (row 2)
- Free entry
One row per employee per day. Formulas in English Excel syntax (comma separator). Times are entered as hh:mm.
Which formulas calculate hours in Excel?
Excel stores a time as a fraction of a day: 12:00 equals 0.5. Three rules avoid most errors:
- [h]:mm for totals: with hh:mm, 176 hours display as 08:00. Month total: =SUM(I2:I32);
- MOD for night shifts: from 22:00 to 06:00, =MOD(H2-E2,1) gives 08:00 instead of a negative duration;
- ×24 for payroll: =(I2-J2)*24 gives −0.5 for a missing half hour, and 7:30 becomes 7.5.
Other useful formulas: days present =COUNTIF(L2:L32,"P"), hours beyond 44 h in the week =MAX(0,SUM(I2:I8)*24-44), lateness in minutes for an 08:30 start =MAX(0,(E2-TIME(8,30,0))*1440). Protect the formula cells (Review > Protect Sheet).
How do you count overtime in the sheet?
Article 201 of the Labour Code pays overtime at +25% between 6 am and 9 pm, +50% between 9 pm and 6 am, and +50% or +100% on the weekly rest day. Each day must therefore be split by time band, which quickly makes the formula hard to check.
For a specific case, use the overtime calculator and the guide to overtime in Morocco.
How do you prepare a printable timesheet?
- 1
One tab per month
One row per day, Sundays greyed out with conditional formatting (=WEEKDAY(A2,2)=7).
- 2
Columns to fill in by hand
Date, in, break, out and the employee’s signature.
- 3
An approval area
Month total, the manager’s name and signature, approval date.
- 4
The page layout
Landscape, one page per employee per month, column headings repeated.
What are the limits of an Excel timesheet?
Excel calculates what it is given correctly. The problem is what it is given, and who can change it.
Declared hours, not recorded ones
A time typed by hand proves nothing in a dispute.
No trace of changes
Anyone can change a cell, and copies drift apart between sites.
Cases that are hard to put in formulas
Nights, rotating schedules, Ramadan, public holidays: one more formula for every rule.
Anomalies show up too late
A forgotten punch is discovered at payroll time.
When should you move from Excel to time and attendance software?
When entering hours takes more than a day a month, or as soon as you have several sites, night shifts or rotating schedules. Time and attendance software records hours at the source (time clock or phone), applies the schedule and Labour Code rules, and flags anomalies as they happen. See how to choose time and attendance software.
How do you move from Excel to TEMPO without starting from scratch?
- Import your employees and the punches already recorded from Excel, with the template provided by TEMPO.
- Record hours at the source: ZKTeco or Hikvision time clocks, or the employee’s phone.
- Let the calculation follow your rules: schedules, nights, Ramadan, overtime, and anomalies every day.
- Export to Excel reports and the activity statement for your payroll software.
Read also
The rules and tools that replace the spreadsheet.
- Time and attendance system Company time and attendance system in Morocco: time clocks or phones, software, calculation rules. How to choose and size it, site by site.
- Time management Time management software in Morocco: calculation modes, grace periods, normal hours and overtime, daily and period reports.
- Legal working hours Legal working hours in Morocco: 44 hours a week, 2,288 hours a year, the 191-hour month, sample schedules and tracking with time and attendance.
- Overtime calculator Free overtime calculator for Morocco: the 25, 50 and 100% rates of article 201, day, night, rest day and public holiday, with the method.
Questions fréquentes
How do you track staff attendance in Excel?
Create one row per employee per day with the date, employee ID, time in, break, time out and status. Calculate hours with =MOD(Out-In,1)-MOD(BreakEnd-BreakStart,1), display totals in [h]:mm format and convert to decimal (×24) for payroll. Protect the formulas and have the month approved.
How do you calculate night hours in Excel?
Use =MOD(H2-E2,1) instead of =H2-E2. When the employee leaves the next day, for example from 22:00 to 06:00, a simple subtraction gives a negative value; MOD adds a day and returns 08:00. Overtime worked between 9 pm and 6 am is paid at +50%.
Why does Excel show ###### in my timesheet?
Because the cell holds a negative duration, which Excel cannot display in time format, for example a difference when the employee worked less than scheduled. Multiply the difference by 24 to show it in decimal hours (−0.5 for half an hour), or test the sign with IF before displaying it.
Is an Excel timesheet enough?
For a few employees on one site with fixed hours, often yes. With several sites, night shifts or rotating schedules, hours declared by hand and untraced changes cause problems. Time and attendance software records hours at the source and keeps the history of corrections.
Can you import an Excel timesheet into TEMPO?
Yes. TEMPO imports employees and punches from Excel with its template: employee ID, date, time, direction (in or out) and note. Each rejected row says what to correct. From then on, hours come from the time clocks or the employees’ phones.
Sources
Textes officiels d'où viennent les chiffres de cette page. Les valeurs proposées par le logiciel restent modifiables.