How to Return Leave Days Between Start and End Dates in Excel
Question details
The user needs an Excel formula to check if a specific calendar date falls between an employee's leave start date and end date, and return the corresponding leave type.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a summary attendance tracker or calendar matrix where daily cells need to dynamically populate with an employee's leave status based on an absence log.
- Observed behavior
- A standard VLOOKUP function can only evaluate exact single-value matches and fails to determine if a target date falls between two date boundaries.
Ensure your absence log contains properly formatted date values for the start and end columns, and that the employee names in your summary perfectly match the names in your data log.
Use FILTER and TEXTJOIN to Extract Leave Types
This advanced array formula checks multiple conditions simultaneously, making it perfect for extracting data based on date boundaries and exact text matches.
The FILTER function can evaluate multiple criteria by multiplying them together, which creates an AND logic. We combine this with the TEXTJOIN function to elegantly handle cases where an employee might have overlapping leave entries on the exact same day.
Click the cell in your summary table where you want the leave type to appear (for example, the cell intersecting the specific employee and the calendar date).
Input the following formula: =IFERROR(TEXTJOIN(", ",TRUE,FILTER('Absence Log'!$F$2:$F$1000,('Absence Log'!$A$2:$A$1000=$B5)*('Absence Log'!$D$2:$D$1000<C$3)*('Absence Log'!$E$2:$E$1000>C$3))),"")
Modify $B5 to point to your employee name cell, and C$3 to point to the calendar date column header. Ensure the 'Absence Log' ranges correspond to your actual data.
Press Enter to execute the formula, then drag the fill handle across your calendar matrix to populate the leave types for all days and employees.
Manage Attendance Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like FILTER and TEXTJOIN, allowing you to build complex attendance trackers and handle complex date range lookups effortlessly.
- 1. Open your attendance tracker: Launch WPS Spreadsheet and open your existing attendance or HR log workbook.
- 2. Apply the array formula: Click on the target cell in your calendar view and paste the IFERROR, TEXTJOIN, and FILTER combination formula.
- 3. Drag to populate: Use the smart fill handle at the bottom right of the cell to drag the formula across the rest of the dates.
Frequently Asked Questions
Why does my FILTER formula return a #CALC! error?
The #CALC! error appears when the FILTER function finds no matching records for that specific date. Wrapping the entire formula in an IFERROR function replaces this error with a blank cell, keeping your calendar clean.
Can I use INDEX and MATCH instead for this date range problem?
While INDEX and MATCH can be adapted to handle multiple criteria using array formulas, it will only return the first match it finds. If there are overlapping leave dates, the TEXTJOIN and FILTER combination is safer because it will list all matching leave types.
How do I count the total number of leave days between two dates?
If you only need a numerical count of working days between two dates rather than looking up the leave type, you can use the NETWORKDAYS function, specifying the start date, end date, and an optional range of holidays.
