WPS Spreadsheet: Mark Employee Leave Dates with IF, AND, and OR
Question details
The user needs a formula that identifies whether each employee was on leave on each date.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- An employee schedule contains employee IDs in rows, dates in columns, and a separate table with employee IDs, leave start dates, and leave end dates. The user wants to fill the schedule with a leave marker.
- Observed behavior
- The worksheet needs to return "Leave" when an employee ID and date match a leave period, and return 5 when no matching leave period exists.
Confirm that the employee ID column and date headers use consistent values, and that the leave dates are stored as real dates rather than text. Convert the leave records to a table and note its name and exact column names, such as LeaveTable, EmployeeID, LeaveStartDate, and LeaveEndDate.
Use IF and OR with COUNTIFS for multiple leave periods
Use this solution when an employee can have more than one leave record and the leave data is stored in a structured table.
COUNTIFS applies the employee ID and date-range conditions together, while OR checks whether at least one matching leave record exists. Structured references continue to include new records when the table is refreshed.
Assume employee IDs are in column A, date headers begin in B1, and the leave table is named LeaveTable. Replace these names if your worksheet uses different headers.
In WPS Spreadsheet, click the first cell where a leave result should appear, such as B2.
Enter =IF(OR(COUNTIFS(LeaveTable[EmployeeID],$A2,LeaveTable[LeaveStartDate],"<="&B$1,LeaveTable[LeaveEndDate],">="&B$1)>0),"Leave",5) and press Enter.
Drag the fill handle from B2 across the date columns. The mixed references keep the employee ID column fixed and move each date header across the formula.
Drag the formula down the employee rows so each row checks its own employee ID.
Use Data > Refresh All after updating the source leave records, then confirm that the structured references still return Leave for dates inside each leave period.
Use IF with AND for one leave period per employee row
Use this simpler formula when each employee row already contains one employee ID, one leave start date, and one leave end date.
Mark employee leave dates in WPS Spreadsheet
WPS Spreadsheet can evaluate employee IDs and date ranges with IF, AND, OR, and COUNTIFS formulas, then fill the results across a schedule. Its table references help the formula continue working when Power Query data is refreshed.
- 1. Open the schedule: Open the workbook in WPS Spreadsheet and locate the employee ID rows, date headers, and leave-record table.
- 2. Enter the formula: In the first result cell, enter =IF(OR(COUNTIFS(LeaveTable[EmployeeID],$A2,LeaveTable[LeaveStartDate],"<="&B$1,LeaveTable[LeaveEndDate],">="&B$1)>0),"Leave",5).
- 3. Fill the schedule: Drag the fill handle across the date columns and down the employee rows.
- 4. Refresh the source data: Choose Data > Refresh All after the leave table changes, then check several dates against the source records.
Frequently Asked Questions
How do I mark a date as Leave when an employee has several leave records?
Use the COUNTIFS formula with LeaveTable[EmployeeID], LeaveTable[LeaveStartDate], and LeaveTable[LeaveEndDate]. COUNTIFS checks all matching records and returns Leave when at least one period contains the schedule date.
Why does the formula return 5 for every date?
Check that the employee IDs match exactly, the date headers are real dates, and the table column names are correct. Extra spaces in IDs and dates stored as text commonly prevent a match.
Can I use AND instead of COUNTIFS?
Yes, use AND when one row contains a single employee ID, start date, and end date. For multiple leave records, AND alone cannot reliably test whether any record matches; COUNTIFS is the better option.
Will the formula continue working after Power Query refreshes the leave table?
Yes, if the source is an Excel-compatible table and the formula uses structured references such as LeaveTable[EmployeeID]. After refreshing, use Data > Refresh All and verify that the table name and column headers were not changed.
