Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallOutdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchThe most reliable Excel leave-record format uses a central Leave Log as the source of truth, supported by an Employees list, a Monthly Summary, and a Lists sheet for drop-down values and holidays. A calendar view can then show each employee’s leave visually.
This design is more accurate and easier to audit than maintaining twelve unrelated monthly sheets. It can track leave types, approval status, monthly totals, cross-month absences, and basic remaining balances without requiring paid HR software.
As an Amazon Associate I earn from qualifying purchases.
What an employee monthly leave record should contain
At minimum, record:
- Employee ID and name
- Department or location
- Leave type
- Start and end dates
- Number of days or hours
- Approval status
- Reason or notes
- Approver
- Leave entitlement and remaining balance
Common leave codes include AL for annual leave, SL for sick leave, CL for casual leave, UL for unpaid leave, H for a public holiday, WO for a weekly off, and HD for half day. Define your own codes because terminology and leave policies vary by employer and country.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Choose the right Excel format
| Format | Best for | Main limitation |
|---|---|---|
| Monthly calendar | Small teams, visual monitoring, printing | Manual entry and weaker reporting |
| Leave transaction log | Multiple employees, balances, payroll review and auditing | Less immediately visual |
| Combined workbook | Most organizations | Requires initial setup |
For a dependable workbook, use the combined approach:
#1 Best Overall
- Employees: employee details and entitlement information.
- Leave Log: one row per leave request or leave period.
- Monthly Summary: calculated totals by employee and month.
- Lists: leave types, statuses, departments, codes and holidays.
- Calendar: an optional visual daily view.
Step 1: Create the Employees sheet
Add a sheet named Employees with these headings:
Employee ID | Employee Name | Department | Joining Date | Annual Entitlement | Opening Balance | Active
Example:
| Employee ID | Employee Name | Department | Joining Date | Annual Entitlement | Opening Balance | Active |
|---|---|---|---|---|---|---|
| E001 | Alex Brown | Sales | 01/01/2026 | 20 | 20 | Yes |
| E002 | Sam Lee | Finance | 15/03/2026 | 18 | 18 | Yes |
Select the range and press Ctrl + T. Confirm that the table has headers, then use Table Design > Table Name to name it Employees. Excel Tables expand when employees are added and make formulas easier to maintain. Microsoft’s Excel guidance covers tables and standard worksheet formulas at Microsoft Support.
Step 2: Create the Leave Log sheet
Add a sheet named Leave Log and create these columns:
Request ID | Employee ID | Employee Name | Leave Type | Start Date | End Date | Days | Status | Reason | Approved By | Submitted Date | Notes
Convert the range to a Table and name it LeaveLog. Enter one leave period per row, rather than one row for every day. For example:
| Employee ID | Employee | Leave Type | Start Date | End Date | Days | Status |
|---|---|---|---|---|---|---|
| E001 | Alex Brown | Annual Leave | 05/08/2026 | 06/08/2026 | 2 | Approved |
| E002 | Sam Lee | Sick Leave | 12/08/2026 | 12/08/2026 | 1 | Approved |
Use the employee ID as the primary selection. Names can be duplicated, misspelled or changed, while an ID provides a more reliable link between sheets.
Step 3: Add lists and drop-down menus
On a Lists sheet, create lists for:
- Leave types: Annual Leave, Sick Leave, Casual Leave, Unpaid Leave, Maternity Leave and Other Leave
- Statuses: Approved, Pending, Rejected and Cancelled
- Departments
- Employee IDs
- Leave codes
- Public holidays
Use Data > Data Validation > Allow: List to create drop-downs for Employee ID, Leave Type and Status. Tables or expanding named ranges are preferable to fixed ranges so newly added employees appear in the menus.
For Microsoft 365 and newer Excel versions, retrieve the employee name with:
=XLOOKUP([@[Employee ID]],Employees[Employee ID],Employees[Employee Name],"")
For older versions, use:
=IFERROR(INDEX(Employees[Employee Name],MATCH([@[Employee ID]],Employees[Employee ID],0)),"")
Step 4: Calculate leave days
For calendar-day leave, count both the first and last date:
=[@[End Date]]-[@[Start Date]]+1
For a standard Monday-to-Friday workweek, use:
=NETWORKDAYS([@[Start Date]],[@[End Date]])
To exclude dates listed in a holiday table named Holidays:
=NETWORKDAYS([@[Start Date]],[@[End Date]],Holidays[Date])
Do not assume weekends are always non-working days. Some organizations operate six-day, rotating or customized schedules. Similarly, whether public holidays count depends on the employer’s policy and applicable jurisdiction.
A safer formula leaves blank records empty and flags invalid dates:
Rank #3
=IF(OR([@[Start Date]]="",[@[End Date]]=""),"",IF([@[End Date]]<[@[Start Date]],"",NETWORKDAYS([@[Start Date]],[@[End Date]],Holidays[Date])))
Add a warning column:
=IF([@[End Date]]<[@[Start Date]],"Invalid date range","")
Step 5: Build the Monthly Summary
Create a sheet named Monthly Summary with columns such as:
Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Clear out junk files and repair common Windows errors3Fix the driver behind crashes, sound loss and screen glitchesEmployee ID | Employee Name | Month | Annual Leave | Sick Leave | Casual Leave | Unpaid Leave | Total Used | Balance | Pending
Enter the first day of the reporting month in a cell such as B1, for example 01/08/2026. Use a real Excel date rather than text such as “August 2026.”
A basic formula for approved annual leave whose start date falls in the selected month is:
=SUMIFS(LeaveLog[Days],LeaveLog[Employee ID],$A2,LeaveLog[Leave Type],"Annual Leave",LeaveLog[Status],"Approved",LeaveLog[Start Date],">="&$B$1,LeaveLog[Start Date],"<"&EDATE($B$1,1))
However, that formula can undercount a leave period that begins in one month and ends in another. For reliable reporting, calculate the overlap between the leave period and the selected month.
For a row with start date in E2, end date in F2, and month start in B1, the calendar-day overlap is:
Free tools Windows power users keep installed
One-click scans. No signup required.
Rank #4
=MAX(0,MIN(F2,EOMONTH($B$1,0))-MAX(E2,$B$1)+1)
For working-day overlap excluding listed holidays:
=NETWORKDAYS(MAX(E2,$B$1),MIN(F2,EOMONTH($B$1,0)),Holidays[Date])
For example, leave from August 29 through September 3 must contribute only its August portion to August and only its September portion to September. Filtering only by start date can produce the wrong result.
To total leave-type columns in the summary:
=SUM(D2:G2)
To count pending requests separately, use the same criteria with Status set to Pending. Normally, only approved leave should reduce the official balance.
Step 6: Calculate the remaining balance
A basic balance calculation is:
=Opening Balance-Total Approved Leave Used
For a simple summary, this may be:
=Employees[@[Opening Balance]]-H2
That is only a basic example. Actual balances may depend on monthly accrual, joining date, probation, carry-forward limits, expired leave, half days, hourly leave, adjustments, encashment and changes in entitlement. Microsoft’s documentation for leave plans illustrates how accrual frequency, prorating, tiers, minimum balances and carry-over rules can materially change the calculation: Microsoft Dynamics 365 leave plans.
Where accruals are important, use a separate balance table:
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →Employee | Period | Opening Balance | Accrued | Used | Adjustments | Closing Balance
=Opening Balance+Accrued-Used+Adjustments
Do not automatically grant a full annual entitlement to employees who join or leave midyear unless that is the company’s policy.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Step 7: Create a visual monthly calendar
- Put the first day of the selected month in
B1. - Place day numbers across row 4.
- List employees down column A.
- Enter approved daily codes such as
AL,SL,CLandH. - Add a visible legend and total columns.
To generate day numbers across up to 31 columns:
=IF(MONTH($B$1+COLUMN(A1)-1)=MONTH($B$1),DAY($B$1+COLUMN(A1)-1),"")
For actual date headers:
=IF(MONTH($B$1+COLUMN(A1)-1)=MONTH($B$1),$B$1+COLUMN(A1)-1,"")
Format the date headers as d or ddd d. Apply conditional formatting to the calendar range, for example B5:AF100, with rules such as:
=B5="AL"
=B5="SL"
=B5="CL"
=B5="H"
Use different colors, but also show text codes because color alone is not reliable for printed reports or color-blind users.
Calendar totals
If one employee’s codes are in B5:AF5:
Annual leave: =COUNTIF(B5:AF5,"AL")
Sick leave: =COUNTIF(B5:AF5,"SL")
Half days: =COUNTIF(B5:AF5,"HD")*0.5
Total leave: =COUNTIF(B5:AF5,"AL")+COUNTIF(B5:AF5,"SL")+COUNTIF(B5:AF5,"CL")+COUNTIF(B5:AF5,"HD")*0.5
Microsoft examples also use COUNTIF and SUMIFS for attendance and leave calculations: Microsoft attendance example.
Handle half-day and hourly leave correctly
A text-only daily calendar cannot represent every leave policy accurately. Options include:
- Use
HD-AMandHD-PM. - Store a numeric quantity such as
0.5. - Add
Leave UnitandLeave Quantitycolumns. - Track hours in a separate field.
- Use separate morning and afternoon columns.
Do not mix days and hours in one balance unless there is a documented conversion rule.
Protect the workbook and maintain it
- Protect formula cells and leave input cells unlocked.
- Keep a backup copy before changing formulas or adding a new year.
- Update the employee, leave-type and holiday tables rather than typing values in multiple sheets.
- Add a unique Request ID to prevent duplicate entries.
- Review pending, rejected and cancelled requests separately.
- Use conditional formatting to flag negative balances, missing IDs and invalid dates.
- Do not store detailed medical diagnoses in the workbook.
- Restrict access to HR and authorized managers.
Excel worksheet protection is not a replacement for role-based security or a formal HR system. Leave records can contain sensitive personal information.
Common errors and fixes
| Problem | Likely cause | Fix |
|---|---|---|
| Formula returns zero | Wrong ID, type, status or date criteria | Check spelling, table names and criteria. |
| Dates behave incorrectly | Dates are stored as text | Test with =ISNUMBER(E2) and reformat or re-enter the dates. |
| Dates display as numbers | Incorrect number format | Use Home > Number Format > Short Date. |
| New employees do not appear | Fixed validation range | Use a Table or expanding named range. |
| Totals are duplicated | Duplicate request or two sources of truth | Use the Leave Log as the source and add Request IDs. |
| Cross-month totals are wrong | Formula checks only Start Date | Use start/end overlap logic. |
| Names do not match across sheets | Manual spelling variations | Look up names from Employee ID. |
When Excel is no longer the right tool
Excel is suitable when leave volume is modest, rules are simple and one or two people maintain the file. Consider dedicated software when the organization needs employee self-service, mobile access, multiple approval levels, automatic accruals, payroll integration, real-time reporting, concurrent editing, a stronger audit trail or role-based permissions.
Vendor capabilities and pricing change, so verify current terms directly. Jibble advertises leave requests, approvals, balances, reporting and Excel or CSV exports at its leave-tracking page. Zoho Creator offers customizable leave workflows and holiday, carry-forward and approval features at its leave tracker page. BambooHR describes broader time-off and attendance features at its time and attendance page.
Quick Recap
Final implementation checklist
- Create Employees, Leave Log, Monthly Summary, Lists and optional Calendar sheets.
- Convert the employee and leave ranges into Excel Tables.
- Use Employee ID as the primary link.
- Add drop-downs for leave type and status.
- Decide whether leave uses calendar days, working days, scheduled days, half days or hours.
- Add the correct holiday list.
- Count only approved leave in the official balance.
- Use overlap formulas for leave spanning two months.
- Protect formulas and back up the workbook.
- Print or export the Monthly Summary only after checking warnings and duplicates.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
