Blog Leave

Leave Tracker Excel: Column Layout, Formulas and Example

The exact sheets, columns and formulas for an Excel leave tracker: day counts, SUMIFS balances, pro-rata leave, who is off today and a monthly view.

By Aajar Team · · 12 min read

Cover card titled 'Excel leave tracker' listing five sheets, NETWORKDAYS.INTL for days, SUMIFS for leave taken, EL accrual of 1.25 a month, and an example EL balance of 8.5 days.

Short answer: A leave tracker Excel workbook needs four sheets: Settings, Employees, a Leave Log with one row per leave request, and a Balance sheet. The log works out days with NETWORKDAYS.INTL, and the balance sheet adds up approved leave with SUMIFS and subtracts it from each person's entitlement. Below is the exact column layout, every formula, and a worked example you can copy.

This guide is about leave, not daily attendance. If you also need a monthly present/absent grid, see our guide to the attendance sheet in Excel and keep the two files separate.

What is a leave tracker and why keep it in Excel?

A leave tracker is a record of who took which leave, on which dates, and how much balance each person has left. It answers three questions quickly: how many days does this person have, who is off this week, and has anyone gone past their quota.

Excel works well for small teams because it is free, everyone knows it, and the formulas are transparent. It starts to strain when many people edit the file, when approvals happen on WhatsApp, or when the team crosses 30 to 40 people.

How do I create a leave tracker in Excel?

Build it as a small database, not as one big calendar. Each sheet has one job, and formulas pull data from the log into the summaries. That way you type each leave only once.

  1. Settings: the leave year start date, an "as-on" date, and the quota for each leave type.
  2. Holidays: your company's holiday dates for the year, one per row.
  3. Employees: employee ID, name, department, date of joining and any carried-forward balance.
  4. Leave_Log: one row per leave request, with dates, type, status and a day count.
  5. Balance: one row per employee, with entitlement, leave taken and balance for each type.
  6. Monthly: optional, a month-by-month view of days taken per person.

Use sheet names without spaces, such as Leave_Log. Formulas stay shorter because Excel does not need quote marks around the sheet name.

What goes on the Settings and Holidays sheets?

Put each setting in column B and give it a name using Formulas > Define Name. Named cells make the formulas easier to read and let you change a rule in one place.

CellLabel (column A)Sample valueDefined name
B1Leave year start01-01-2026YearStart
B2As-on date31-10-2026AsOn
B3Casual leave per year8CL_Year
B4Sick leave per year7SL_Year
B5Earned leave per month1.25EL_Month
Holidays!A2:A50Holiday dates26-01-2026, 15-08-2026, 02-10-2026Holidays

The quotas above are sample numbers. Use the figures from your own leave policy, and check the earned leave rules that apply in your state before you settle on an accrual rate.

What columns should a leave tracker Excel sheet have?

The Leave_Log is the heart of the file. Keep row 1 for headings and start entries from row 2. Turn it into an Excel Table (Ctrl+T) so new rows pick up the formulas automatically.

ColumnHeadingWhat to enter
AEmp IDTyped, for example E014
BNameFormula, looked up from Employees
CLeave typeDrop-down: CL, SL, EL, LWP
DFromFirst day of leave
EToLast day of leave
FHalf dayDrop-down: Yes or No
GDaysFormula, working days counted
HStatusDrop-down: Approved, Pending, Rejected
IApproved byManager's name
JRemarksReason or note, kept short

Add the drop-downs with Data > Data Validation > List. Fixed lists stop people typing "cl", "Casual" and "C.L." for the same thing, which would break every SUMIFS later.

How do I pull the employee name automatically?

In B2 of the Leave_Log, enter =IFERROR(VLOOKUP(A2,Employees!$A$2:$B$300,2,FALSE),""). If you are on Microsoft 365, =XLOOKUP(A2,Employees!$A$2:$A$300,Employees!$B$2:$B$300,"") does the same job.

A blank name tells you the employee ID was mistyped. That small check catches many errors before they reach the balance sheet.

How do I calculate leave days in Excel excluding Sundays and holidays?

Use NETWORKDAYS.INTL, which counts working days between two dates and skips the weekly off and any holiday list you give it. In G2 enter:

=IF(F2="Yes",0.5,NETWORKDAYS.INTL(D2,E2,"0000001",Holidays))

The seven-character text sets the weekly off, starting from Monday; a 1 means "off". So "0000001" means Sunday off only, and "0000011" means Saturday and Sunday off. If a half day is marked, the formula returns 0.5, so enter the same date in From and To for half days.

What if our policy uses the sandwich rule?

Under a sandwich rule, weekly offs and holidays that fall between two leave days are counted as leave. In that case count calendar days instead, with =E2-D2+1 in G2. Our sandwich leave policy guide explains when companies apply it and how to word it.

Whichever rule you use, apply it to every row. Mixing the two methods in one log is the most common reason balances stop matching what employees expect.

How do I calculate leave balance in Excel with SUMIFS?

On the Balance sheet, put one employee per row with the ID in column A. Then add three columns per leave type: entitled, taken and balance. The "taken" formula adds the days column for that employee and leave type, counting only approved leave inside the leave year.

For casual leave taken, in E2 enter:

=SUMIFS(Leave_Log!$G$2:$G$1000,Leave_Log!$A$2:$A$1000,$A2,Leave_Log!$C$2:$C$1000,"CL",Leave_Log!$H$2:$H$1000,"Approved",Leave_Log!$D$2:$D$1000,">="&YearStart,Leave_Log!$D$2:$D$1000,"<="&AsOn)

Copy it across for SL and EL and change only "CL" in the formula. The balance is then simply entitled minus taken, for example =D2-E2.

ColumnHeadingFormula in row 2
AEmp IDTyped
BName=VLOOKUP(A2,Employees!$A$2:$E$300,2,FALSE)
CDate of joining=VLOOKUP(A2,Employees!$A$2:$E$300,4,FALSE)
DCL entitled=ROUNDDOWN(CL_Year*(12-MONTH(MAX(C2,YearStart))+1)/12*2,0)/2
ECL takenSUMIFS formula above with "CL"
FCL balance=D2-E2
G to ISL entitled, taken, balanceSame pattern with SL_Year and "SL"
JEL opening=VLOOKUP(A2,Employees!$A$2:$E$300,5,FALSE)
KEL accrued=IFERROR(DATEDIF(MAX(C2,YearStart),AsOn+1,"m"),0)*EL_Month
LEL takenSUMIFS formula with "EL"
MEL balance=J2+K2-L2
NPending requests=COUNTIFS(Leave_Log!$A$2:$A$1000,$A2,Leave_Log!$H$2:$H$1000,"Pending")

How does pro-rata leave for new joiners work in these formulas?

The CL entitled formula gives a full quota to anyone who joined before the leave year. For someone who joined mid-year, it counts the months left including the joining month, scales the quota, and rounds down to the nearest half day.

The EL accrued formula counts completed months from the later of the joining date and the year start, up to the as-on date. Adding 1 to AsOn means a month is credited on its last day. Change the as-on date in Settings and every balance updates.

How do I flag employees who have gone over their leave?

Select the balance columns and add a conditional formatting rule: Home > Conditional Formatting > Highlight Cells Rules > Less Than 0. A negative balance turns red, and HR can decide whether the extra days become leave without pay or come out of another leave type.

Worked example: leave tracker for a 26-person agency in Kochi

Take a 26-person digital marketing agency in Kochi with a January to December leave year. Its policy gives 8 CL and 7 SL on 1 January, and earned leave at 1.25 days for each completed month. Sunday is the weekly off, and the sample holiday list has 26 January, 15 August and 2 October 2026.

Ritu, an accounts executive who joined in 2023, carried forward 6 EL into 2026. Here is her Leave_Log, with the Days column worked out by the NETWORKDAYS.INTL formula.

TypeFromToHalf dayStatusDays
CLThu 12-02-2026Fri 13-02-2026NoApproved2
SLMon 06-04-2026Mon 06-04-2026NoApproved1
ELMon 11-05-2026Sat 16-05-2026NoApproved6
CLTue 21-07-2026Tue 21-07-2026YesApproved0.5
ELWed 30-09-2026Mon 05-10-2026NoApproved4
SLTue 27-10-2026Tue 27-10-2026NoPending1

The last EL row spans six calendar days. Sunday 4 October is the weekly off and Friday 2 October is a holiday, so the formula counts 6 − 2 = 4 days.

With the as-on date set to 31 October 2026, her balance sheet works out like this:

  • CL: entitled 8, taken 2 + 0.5 = 2.5, balance 8 − 2.5 = 5.5 days.
  • SL: entitled 7, taken 1 (the pending request is not counted), balance 7 − 1 = 6 days.
  • EL: accrued 10 months × 1.25 = 12.5, taken 6 + 4 = 10, balance 6 + 12.5 − 10 = 8.5 days.
  • Pending requests: 1.

Now a new joiner. Arjun joined on 16 June 2026, so 7 months of the leave year are left. His CL entitlement is 8 × 7 ÷ 12 = 4.67, rounded down to 4.5.

His SL is 7 × 7 ÷ 12 = 4.08, rounded down to 4. His EL accrued by 31 October is 4 completed months (16 June to 16 October) × 1.25 = 5 days.

Arjun took CL from Friday 14 August to Monday 17 August. Saturday 15 August is a holiday and Sunday is off, so the normal formula counts 2 days. Under a sandwich rule, =E2-D2+1 would count 4 days instead, leaving him with 0.5 CL rather than 2.5.

How do I see who is on leave on a given day?

Put a date in A1 of a small "Today" sheet and use COUNTIFS to count approved leaves that cover it:

=COUNTIFS(Leave_Log!$D$2:$D$1000,"<="&A1,Leave_Log!$E$2:$E$1000,">="&A1,Leave_Log!$H$2:$H$1000,"Approved")

Replace A1 with TODAY() for a live count. In the example, the formula returns 1 for 2 October 2026 because Ritu's EL covers it, even though it is a holiday. To list names in Microsoft 365, use =FILTER(Leave_Log!B2:B1000,(Leave_Log!D2:D1000<=A1)*(Leave_Log!E2:E1000>=A1)*(Leave_Log!H2:H1000="Approved"),"Nobody").

How do I make a monthly leave summary in Excel?

On a Monthly sheet, put the first day of each month across row 1 (01-01-2026, 01-02-2026 and so on) and employee IDs down column A. In B2 enter:

=SUMIFS(Leave_Log!$G$2:$G$1000,Leave_Log!$A$2:$A$1000,$A2,Leave_Log!$H$2:$H$1000,"Approved",Leave_Log!$D$2:$D$1000,">="&B$1,Leave_Log!$D$2:$D$1000,"<="&EOMONTH(B$1,0))

Copy it across and down. For Ritu this gives 2 in February, 1 in April, 6 in May, 0.5 in July and 4 in September.

Notice that the 4 EL days spanning 30 September to 5 October all land in September, because the formula uses the start date. If payroll needs exact days per month, split such leaves into two rows at the month end.

What are common mistakes in Excel leave trackers?

  • Dates typed as text: "12.02.2026" is not a date to Excel. Format the From and To columns as Date and use Data Validation to accept dates only.
  • Counting pending leave: leave the Status filter in every SUMIFS, or pending and rejected requests will reduce balances.
  • Hard-coded quotas: typing 8 inside formulas means editing hundreds of cells when the policy changes. Keep quotas on the Settings sheet.
  • No year-end close: at year end, copy closing EL balances into the EL opening column (as values), apply your carry-forward cap, and start a new log.
  • Many editors, no history: Excel does not show who changed a balance and why. Protect formula columns and keep one person as the file owner.

Before you build, write the rules down. Our leave policy template covers quotas, accrual, carry-forward and approvals, which are the inputs this workbook needs.

When should you move from a leave tracker in Excel to an app?

A spreadsheet stays manageable when one person keeps it and requests are few. Signs you have outgrown it: employees keep asking HR for their balance, approvals sit in chats and emails, two versions of the file are in circulation, or month-end payroll waits on someone reconciling leave by hand.

At that stage, a system where employees apply, managers approve and balances update on their own saves more time than any formula can.

Managing leave balances on Aajar

On Aajar, HR sets up leave types with their own policies, balances and leave cycle, along with a holiday calendar, weekly offs and an optional sandwich leave rule. Employees request leave from their phone or computer, managers approve it, and everyone sees the same balance without opening a spreadsheet.

Approved leave shows in attendance, and payroll is run from the same records. The Growth plan adds Excel exports and bulk uploads, so you can bring in your employee list or keep a leave tracker Excel copy alongside. Plans start from ₹20 per user per month, billed yearly, and every plan is free for 3 months for teams of up to 50 users; see Aajar pricing.

Frequently asked questions

Is there a free leave tracker template in Excel?

You can build one in under an hour with the layout above: Settings, Holidays, Employees, Leave_Log and Balance sheets. Building it yourself means the quotas and rules match your own policy rather than someone else's defaults.

How do I calculate half-day leave in Excel?

Add a Half day column with Yes or No, and use =IF(F2="Yes",0.5,NETWORKDAYS.INTL(D2,E2,"0000001",Holidays)) for the day count. Enter the same date in From and To so the half day is not mixed with full days.

How do I exclude second and fourth Saturdays in an Excel leave tracker?

The weekend code in NETWORKDAYS.INTL covers only fixed weekdays, so alternate Saturdays cannot go there. Add the off Saturdays to the Holidays list instead, and the formula will skip them.

Can Google Sheets run the same leave tracker formulas?

Yes. SUMIFS, COUNTIFS, NETWORKDAYS.INTL, DATEDIF, EOMONTH and FILTER all work in Google Sheets. Named ranges are set under Data > Named ranges.

How do I carry forward leave balance to the next year in Excel?

At year end, copy the EL balance column and paste it as values into the EL opening column of the new year's file. Use =MIN(M2,30) or your own cap to limit what carries forward, then clear the old log rows.

#Leave tracker #Excel templates #Leave balance #Leave management #SUMIFS formula #HR spreadsheets