How to Map Annual Leave Data to an Excel Calendar in WPS Spreadsheets
Question details
The user needs to display full-day and half-day leave codes in an Excel calendar that is populated from an annual leave table.

- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user maintains an annual leave table with employee names, leave start and end dates, and a Code column where H means full-day and h means half-day. They want to map these codes onto a yearly Excel calendar for each employee.
- Observed behavior
- Leave codes need to appear automatically in the correct calendar cells while weekends remain blank.
Before you start, confirm that the workbook contains a leave table with columns for Employee, First Day of Leave, Last Day of Leave, and Code. Also verify that the calendar column headers use real dates and that the staff list and calendar range are defined as named ranges.
Use a Dynamic Array Formula with MAKEARRAY to Fill the Excel Calendar
Use this method when you want a single formula to fill every employee and date cell automatically while excluding weekends and filtering the leave table.
This formula is designed for Microsoft 365-style dynamic arrays and works in supported WPS Spreadsheets versions. It uses LAMBDA to loop through the rows in the Staff named range and the columns in the AllYear date range. For each combination it checks the weekday, then filters Table1[Code] for rows where the employee and date range match. The WEEKDAY function with return type 2 treats Monday as 1, so any weekday greater than 5 is left blank.
In WPS Spreadsheets, open the workbook that contains the annual leave table and calendar. Confirm that the employee list is named Staff, the calendar date range is named AllYear, and the leave data is formatted as Table1 with a Code column.
Click the first calendar cell where the dynamic array should start. This is usually the cell under the first employee name and to the right of the first calendar date.
Paste the following formula into the formula bar and press Enter: =LET(rows,ROWS(Staff),cols,COLUMNS(AllYear),Leave,LAMBDA(r,c,LET(date,INDEX(AllYear,,c),person,INDEX(Staff,r),IF(WEEKDAY(date,2)>5,"",FILTER(Table1[Code],(Table1[Employee]=person)*(date>=Table1[First Day of Leave])*(date<=Table1[Last Day of Leave]),"")))),MAKEARRAY(rows,cols,Leave))
WPS Spreadsheets fills the full grid automatically. Check that full-day leave shows H, half-day leave shows h, and weekends remain blank.
If your named ranges or table name differ, replace Staff, AllYear, and Table1 in the formula with your actual names.

Build an Automated Leave Calendar in WPS Spreadsheets
Map annual leave codes to an Excel calendar with a single dynamic array formula in WPS Spreadsheets. You get automatic H and h code display, weekend exclusion, and live updates without helper columns or manual cell filling.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office and open the Excel workbook that contains the annual leave table and yearly calendar.
- 2. Set up named ranges: Select the employee list and define it as Staff. Select the calendar column date range and define it as AllYear.
- 3. Format the leave table: Convert the annual leave data to an Excel Table named Table1 and make sure it contains Employee, First Day of Leave, Last Day of Leave, and Code columns.
- 4. Enter the dynamic array formula: Click the first calendar output cell, paste the MAKEARRAY formula, and press Enter to spill the leave codes across the calendar.
- 5. Save as .xlsx: Save the workbook as an .xlsx file to keep it fully compatible with Microsoft Excel and WPS Spreadsheets.

Frequently Asked Questions
Can I use this formula if my leave table is not an Excel Table?
Yes. Replace Table1[Code], Table1[Employee], Table1[First Day of Leave], and Table1[Last Day of Leave] with normal range references such as $B$2:$B$100. The rest of the formula works the same.
Why does the formula return an empty string on Saturdays and Sundays?
WEEKDAY(date,2) returns 1 for Monday through 7 for Sunday. The condition >5 leaves blanks for any day with a weekday number greater than 5, which means Saturday and Sunday.
What do the H and h codes represent in the calendar?
The Code column uses H for a full-day leave and h for a half-day leave. The formula filters and displays whatever code exists in the matching row of the leave table.
Does this formula work in WPS Spreadsheets and Microsoft Excel?
This formula uses dynamic array functions such as LET, LAMBDA, FILTER, and MAKEARRAY. It works in Microsoft 365 and in WPS Spreadsheets versions that support these functions. If your WPS version does not support LAMBDA, update WPS Office or use helper-column alternatives.




