Reliable leave management Excel workbooks need four tabs (employees, leave, public holidays, planning), a NETWORKDAYS.INTL formula that counts working days excluding Sundays and public holidays, a SUMIFS formula that adds up approved leave, and a balance equal to days accrued minus days taken. Here is the structure to copy, the formulas in English and French, the statutory entitlements in six African countries and the point where a spreadsheet is no longer enough. Updated 11 November 2026.
At a glance:
- One structured table per tab (Insert > Table), never loose ranges: formulas follow when you add a row.
- Days counted: =NETWORKDAYS.INTL(C2,D2,11,Holidays[Date]), where code 11 removes Sunday only.
- Days taken: SUMIFS on the employee ID, the type “Paid leave” and the status “Approved”.
- Accrual: 2 working days a month in Senegal and Benin, 2.2 in Ivory Coast, 1.5 in Cameroon and Morocco, 1 in the DRC.
- Drop-down lists and conditional formatting to limit input errors and flag negative balances.
- Beyond a few dozen employees or two sites, file versions and approval by email become the real problem.
Why leave management Excel files are still the starting point
Most SMEs start with a workbook: it is already installed and it is enough for a team of ten or twenty people. The problem is how it is built. A file where someone types “5 days” next to a name, with no start or end date, cannot recalculate a balance, answer a labour inspector or feed the leave allowance into payroll. A well-designed leave management Excel workbook follows one rule: you enter dates, Excel computes the days. The general method (reference period, schedule, approval) is covered in our leave management guide; this article focuses on the workbook itself.
The leave management Excel template, tab by tab
Here is the leave management Excel template to copy. Turn each list into a structured table (Insert > Table) and name it in the Table Design tab: Employees, Leave, Holidays, Lists. References such as Leave[Days] follow the table as it grows.
| Tab (table name) | Columns to create | Purpose |
|---|---|---|
| Settings | B1: start date of the reference period (name the cell PeriodStart) | A single date to change each year |
| Employees (Employees) | ID, Name, Site, Country, Hire date, Monthly accrual, Seniority days, Carry-over, Months accrued, Accrued, Taken, Balance | One employee per row, balance calculated |
| Leave (Leave) | ID, Type, Start, End, Days, Status, Approved by, Approval date, Comment | One request per row, never a running total |
| Holidays (Holidays) | Date, Name, Country | Days excluded from the count |
| Lists (Lists) | Leave types, Statuses, Sites | Source of the drop-down lists |
| Planning | Employee IDs in rows, dates in columns | Team view and overlaps |
In leave management Excel files, the number of days is never typed in: it is calculated from the dates.
The leave management Excel formulas
The formulas assume an English version of Excel (comma separator); in a French version the function names change and the separator becomes a semicolon. All of them are documented by Microsoft, for example on the NETWORKDAYS.INTL function page.
Counting the days of an absence
In the Days column of the Leave tab: =NETWORKDAYS.INTL([@Start],[@End],11,Holidays[Date]) (French: =NB.JOURS.OUVRES.INTL([@Start];[@End];11;Holidays[Date])). The function counts both the start and the end date. The third argument sets the weekend: 1 removes Saturday and Sunday, 11 removes Sunday only, and a seven-character string such as "0000001" handles any other calendar (1 = non-working day, starting on Monday).
This choice matters. In Senegal, the 2026 Labour Code defines a working day (jour ouvrable) as any day of the week except the weekly rest day and public holidays (article 3). Entitlements in Senegal, Ivory Coast, Cameroon, Benin and the DRC are expressed in jours ouvrables: Saturday counts. NETWORKDAYS, without “.INTL”, always removes Saturday and therefore undercounts the days taken. In Morocco, the Code refers to days of actual work: align the argument with the company’s real calendar.
Calculating days accrued
In the Employees tab, Months accrued column: =DATEDIF(MAX([@[Hire date]],PeriodStart),TODAY(),"m") (French: =DATEDIF(MAX(...);AUJOURDHUI();"m")). DATEDIF returns the number of complete months; MAX avoids counting months before hiring. Accrued column: =[@[Months accrued]]*[@[Monthly accrual]]+[@[Seniority days]]+[@[Carry-over]]. Months of absence that do not count as actual service (unpaid leave, for instance) must be removed by hand: record them in the comment.
Adding up days taken and getting the balance
The simplest version, =SUMIF(Leave[ID],[@ID],Leave[Days]) (SOMME.SI), adds up every absence, sick leave included. To keep only approved paid leave: =SUMIFS(Leave[Days],Leave[ID],[@ID],Leave[Type],"Paid leave",Leave[Status],"Approved") (French: SOMME.SI.ENS, same argument order, the range to sum first). The balance is then =[@Accrued]-[@Taken].
Filling in the team planning
In the Planning tab, with employee IDs in column A and dates in row 2: =COUNTIFS(Leave[ID],$A3,Leave[Start],"<="&C$2,Leave[End],">="&C$2,Leave[Status],"Approved") (French: NB.SI.ENS). The cell returns 1 if the employee is absent that day. A total row per column (=SUM(C3:C40)) gives the number of people absent each day.
Making the file reliable: drop-downs, checks and colours
Three settings prevent most errors in leave management Excel workbooks.
- Drop-down list: select the Type column, then Data > Data Validation > Allow: List, and point the source to the column in the Lists tab. Do the same for Status (Requested, Approved, Rejected, Cancelled) and Site. Otherwise a typo (“paid leav”) would drop the row from the SUMIFS calculation.
- Date check: on the End column, a custom validation =D2>=C2 rejects an end date earlier than the start.
- Conditional formatting: Home > Conditional Formatting > New Rule > Use a formula. On the Balance column, =$L2<0 in red; on the planning, =C3>0 colours days of absence; on the total row, an orange rule above the number of absences the team can absorb.
The Holidays tab deserves particular care. Religious holidays follow the lunar calendar and move every year; some are only set a few days in advance. Update the tab at the start of the year from the official list, with a Country column if you have several subsidiaries. A forgotten holiday means a day of leave wrongly deducted.
Entitlements to set up, country by country
The Monthly accrual column depends on the country. The table below gives the basic statutory regime, excluding seniority increases and more favourable collective agreement provisions, which vary and go in the Seniority days column.
| Country | Accrual | Annual base | Text |
|---|---|---|---|
| Senegal | 2 working days (jours ouvrables) a month | 24 days | 2026 Labour Code (Law No. 2026-18), art. 249 |
| Ivory Coast | 2.2 working days a month | 26.4 days | Law No. 2015-532, art. 25.1 |
| Cameroon | 1.5 working days a month | 18 days | Law No. 92/007, art. 89 |
| Benin | 2 working days a month | 24 days | Law No. 98-004, art. 158 |
| DRC | at least 1 working day a month | 12 days | Law No. 015/2002, art. 141 |
| Morocco | 1.5 days of actual work a month, after 6 months of continuous service | 18 days | Law No. 65-99, art. 231 |
These figures apply in 2026; carry-over, splitting and the leave allowance are covered in our pages on leave management in Senegal and leave management in Ivory Coast, and for all six countries in the leave management Africa comparison. Employees under 18 have a higher entitlement in most of these countries: give them their own value in the Monthly accrual column rather than a single formula.
The limits of leave management Excel files
A well-built leave management Excel workbook holds up for a stable team on one site. Its limits appear as you grow.
- Versions: “leave_2026_v3_final.xlsx” sent by email, edited by two people, and nobody knows which balance is right. Online co-authoring reduces the risk but does not record who approved what.
- Approval: the “Approved” status is a cell any user of the file can change. There is no approval workflow and no time-stamped record to rely on in a dispute.
- Real-time balance: employees cannot see their balance; they ask HR, who open the file, which has to be up to date.
- Multiple sites and countries: a different monthly accrual, holiday calendar and sometimes rest day per country multiply columns and exceptions.
- Payroll: days taken and the leave allowance have to be copied into the payroll software, with a risk of discrepancy every month.
- Personal data: sick leave recorded in a file circulating by email raises a confidentiality issue.
When to move from leave management Excel to software
The signals: hours spent every month reconciling balances, requests lost in inboxes, two or more sites, field staff with no access to the file, a history rebuilt for an inspection. Our article HR software or Excel sets out the thresholds for switching.
Socium’s Time module follows the same logic without the files: employees submit requests from the mobile app and see their balance there, managers approve them through an approval workflow, and approved absences flow straight into payroll, with each country’s rules. To move from leave management Excel files to time and absence software, keep your workbook: its Employees tab (entitlements, carry-overs, hire dates) becomes your migration file.
Frequently asked questions
Which formula should I use for leave management Excel in working days?
NETWORKDAYS.INTL with code 11, which removes Sunday only, and the holidays range as the fourth argument. NETWORKDAYS also removes Saturday and gives a Monday-to-Friday count.
How do I calculate the balance in a leave management Excel file?
Balance = days accrued (months of service × monthly accrual + seniority + carry-over) minus days taken, obtained with SUMIFS on the employee’s approved paid leave.
How do I exclude public holidays from the count?
List them in a dedicated tab, as a table, and pass the Date column as the last argument of NETWORKDAYS.INTL. Update it every year.
Is there a free leave management Excel template?
The structure table in this article serves as one: copy the tabs and columns, then paste the formulas. Any leave management Excel template has to be adapted to your country’s monthly accrual and public holidays.
How many days of leave does an employee accrue each month?
Under the basic statutory regime in 2026: 2 working days in Senegal and Benin, 2.2 in Ivory Coast, 1.5 in Cameroon and Morocco (days of actual work), at least 1 in the DRC. Collective agreements and seniority can add days.
How do I stop two team members being away at the same time?
With the Planning tab: COUNTIFS marks who is absent each day, a total row adds them up and conditional formatting flags when the threshold is exceeded.
Key takeaways
- Reliable leave management Excel files rely on dates entered and days calculated, never the other way round.
- NETWORKDAYS.INTL with code 11 and a Holidays tab respects the working-day (jour ouvrable) count.
- SUMIFS, drop-down lists and conditional formatting secure the balances.
- The monthly accrual is set per country and checked every year.
If your balances live in several files or several countries, book a demo: we will start from your current workbook.
Want to go further?
See how Socium transforms HR and payroll practices across 12 African countries.