Others

How to Exclude Swipe Records During Employee Leave Dates

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to filter out employee badge swipe records that occur when the employee is officially on leave, based on leave start and end dates.

Product: Database / Spreadsheet
Device & OS: not provided
Scenario: Analyzing employee attendance data where some swipes erroneously or incidentally occur during approved leave dates.
Observed behavior: Swipe records during leave dates need to be excluded from the main attendance analysis, while retaining them for potential HR review.

Before you start

Ensure you have two separate data tables ready: one containing employee swipe records with exact dates, and another containing employee leave start and end dates.

Solution 1Recommended

Use a NOT EXISTS SQL Query

Utilize a NOT EXISTS subquery to filter out swipe records that fall between an employee's documented leave start and end dates.

This method compares your swipe data table with your leave data table. It efficiently excludes any row where the employee ID matches and the swipe date falls within the designated leave period.

Automatically excluding these records may hide important exceptions. Investigate whether the employee actually worked, someone else used the badge, the equipment malfunctioned, or another issue occurred.

Identify Table Names and Columns

Determine the names of your tables. For example, use [Table 1] for swipe records containing 'EmpID' and 'SwipeDate', and [Table 2] for leave records containing 'EmpID', 'Leave Start Date', and 'Leave End Date'.

Execute the Filtering Query

Run the following SQL query in your database interface: SELECT * FROM [Table 1] AS T1 WHERE NOT EXISTS (SELECT * FROM [Table 2] AS T2 WHERE T2.EmpID=T1.EmpID AND T1.SwipeDate BETWEEN T2.[Leave Start Date] AND T2.[Leave End Date]) ORDER BY T1.SwipeDate;

HR Review Recommended: Before permanently omitting these records from your final reports, HR should review these exceptions as a swipe during leave could indicate unauthorized work activity or badge misuse.

Efficient Data Management

Filter Employee Attendance Data with WPS Spreadsheet

Easily manage employee swipe records and leave dates without complex SQL queries using WPS Spreadsheet. With built-in advanced formulas and data filtering tools, you can seamlessly flag and exclude specific date ranges.

1. Organize Your Data: Import your swipe records and leave records into two separate worksheets (e.g., 'Swipes' and 'Leave') within a WPS Spreadsheet.
2. Apply the COUNTIFS Formula: In the 'Swipes' sheet, add a helper column. Use a formula like =COUNTIFS(Leave!A:A, A2, Leave!B:B, "<="&B2, Leave!C:C, ">="&B2) to check if the swipe date (B2) falls within the leave dates for that employee (A2).
3. Filter Out Leave Records: Select the headers, navigate to the 'Data' tab, and click 'Filter'. Filter the helper column to show only the records that return a 0 (meaning the swipe did not occur during leave).

Easily analyze attendance data with powerful spreadsheet functions like COUNTIFS.Fully compatible with Microsoft Excel formats (.xlsx, .xls) for seamless data sharing.Process large datasets quickly with an intuitive and lightweight interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why would an employee have a swipe record during a leave period?

An employee might swipe their badge during leave for a brief office visit, due to an equipment malfunction, or someone else might have used their badge. This is why HR should audit excluded records.

Can I filter these records without using a database query?

Yes. If you are using a spreadsheet program like WPS Spreadsheet, you can use functions like COUNTIFS to check if a swipe date falls between a leave start and end date, and then filter out the matching rows.

Are the records deleted when using the NOT EXISTS query?

No, the NOT EXISTS query simply hides the matching records from the final result set. The original data remains intact in the database for auditing and compliance purposes.

Office Quick Solutions