Function Problems

How to Find Employees with 10 or More Consecutive Attendance Days in Excel

Steve KSteve K Nov 11, 2026 868 views

Question details

The user wants to identify employees who have logged 10 or more consecutive days of attendance using Excel formulas.

How to Find Employees with 10 or More Consecutive Attendance Days in Excel
Product
Excel
Device & OS
not provided
Scenario
Tracking employee attendance records to find continuous streaks of 10 or more days marked as present.
Observed behavior
Evaluating each attendance row dynamically to return a 'Yes' or 'No' result, or extracting the names of the employees who meet the 10-day streak criteria.
Before you start

Ensure your attendance data is organized in a table layout, with employee names in the first column and their daily attendance status (such as 'P' or 'PP') spanning across the adjacent columns in the same row.

Solution 1Recommended

Use BYROW and LAMBDA to Check Consecutive Streaks

Utilize a dynamic array formula to concatenate the row's attendance records and check for a specific text string representing 10 consecutive presence markers.

This method uses Microsoft 365 dynamic array functions to process the entire schedule at once without needing a helper column. It combines CONCAT to merge the row's values and XMATCH with wildcards to find the streak.

1
Select the result cell

Click on the cell at the top of an empty column next to your data where you want the 'Yes/No' results to appear.

2
Input the BYROW formula

Type the following formula: =LET(ten_consecutive, REPT("PP", 10), check, LAMBDA(each_row, LET(joined, CONCAT(each_row), streak, XMATCH("*" & ten_consecutive & "*", joined, 2), IF(ISNUMBER(streak), "Yes", "No"))), BYROW(schedule, check))

3
Adjust the parameters

Replace 'schedule' with your actual data range (e.g., B2:AE50) and change 'PP' to whatever code you use for 'Present'.

4
Execute the formula

Press Enter. The formula will spill down automatically, outputting 'Yes' for employees with a 10-day streak and 'No' for those without.

Use BYROW and LAMBDA to Check Consecutive Streaks
Dynamic Spill: Because BYROW is a dynamic array function, you only need to enter it in the top cell. It will automatically populate the results for all rows in your specified range.

Track Employee Attendance Effortlessly in WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array functions like BYROW, LAMBDA, SCAN, and FILTER. You can easily calculate consecutive attendance streaks without worrying about compatibility issues, making HR and admin tasks highly efficient.

  1. 1. Open your attendance tracker: Launch WPS Spreadsheet and open your employee attendance workbook.
  2. 2. Apply the dynamic formula: Select the target cell and paste your preferred dynamic array formula (e.g., BYROW or FILTER).
  3. 3. Process the data instantly: Press Enter to instantly calculate the consecutive streaks across your entire employee roster.
  4. 4. Save and share seamlessly: Save your file in the standard .xlsx format to ensure seamless sharing with your colleagues.
Fully compatible with Microsoft Excel formulas and .xlsx file formatsSupports modern dynamic array functions for complex data trackingLightweight, fast, and ideal for processing large employee attendance datasetsFree to use with a familiar, user-friendly interface
microsoft office alternative - wps office

Frequently Asked Questions

Can I use these formulas in older versions of Excel?

No, functions like BYROW, LAMBDA, SCAN, and FILTER are dynamic array functions available only in Microsoft 365, Excel 2021, or modern spreadsheet apps like WPS Office. For older versions, you might need complex legacy array formulas using FREQUENCY or VBA macros.

What if my attendance code is just 'P' instead of 'PP'?

You can easily adjust the formula. In the BYROW formula, simply change REPT("PP", 10) to REPT("P", 10). Ensure the text string perfectly matches your data entry format.

How do I handle weekends or holidays in the consecutive count?

If weekends and holidays should not break the streak, you will need to exclude those columns from your range or use a more advanced conditional formula to ignore non-working days before counting the consecutive 'Present' days.

Why does my FILTER formula return a #CALC! error?

The #CALC! error in the FILTER function occurs when no records match your criteria. In this context, it means no employee has achieved 10 or more consecutive attendance days. You can add the optional [if_empty] argument, like =FILTER(A2:A4, R2:R4>=10, "No matches"), to display a custom message instead of an error.