How to Track Tardies in Rolling 30-Day and 90-Day Periods in Spreadsheet
Question details
The user needs to create a spreadsheet that counts employee tardies over dynamic 30-day and 90-day windows to evaluate and trigger appropriate policy warnings.

- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Managing HR or team attendance policies where disciplinary actions are based on the number of infractions occurring within specific rolling timeframes.
- Observed behavior
- A dynamic formula setup is required to automatically calculate rolling date windows and assign verbal, written, final, or termination warnings based on policy thresholds.
Ensure your spreadsheet has clear, standardized columns for Employee Name and Tardy Date, and verify that all dates are properly formatted as Date values rather than text.
Automate Rolling Tardy Tracking using COUNTIFS and IFS
Use logical date functions to calculate tardies dynamically within specific rolling periods and trigger automatic policy warnings.
By leveraging date-based COUNTIFS formulas, your spreadsheet can look backward from any given date to count occurrences within a specific window (like 30 or 90 days). You can then nest this logic with an IFS statement to assign disciplinary actions automatically.
Create clear headers in your spreadsheet. Use Column A for 'Employee Name', Column B for 'Tardy Date', Column C for '30-Day Count', Column D for '90-Day Count', and Column E for 'Warning Status'.
In cell C2 (the 30-Day Count column), enter the formula: =COUNTIFS(A:A, A2, B:B, ">="&B2-30, B:B, "<="&B2). This counts how many times the specific employee was tardy in the 30 days leading up to the current row's date.
In cell D2 (the 90-Day Count column), use a similar formula adjusted for the 90-day window. If tracking from a fixed start date (e.g., cell H1), use: =COUNTIFS(A:A, A2, B:B, ">="&$H$1, B:B, "<="&$H$1+90).
In cell E2 (Warning Status), use an IFS formula to evaluate the counts. For example: =IFS(C2>=5, "Termination", C2>=4, "Final Warning", C2>=3, "Written Warning", C2>=2, "Verbal Warning", TRUE, "None"). Drag all formulas down to apply them to your entire dataset.
Build Dynamic Employee Tardy Trackers with WPS Spreadsheet
WPS Spreadsheet provides comprehensive support for advanced logical and date functions like COUNTIFS and IFS, making it the perfect tool to build automated HR and attendance trackers.
- 1. Create a new tracker: Open WPS Spreadsheet and click 'Blank' to create a new workbook for your attendance data.
- 2. Format dates correctly: Highlight your date column, right-click, select 'Format Cells', and apply the proper 'Date' formatting to ensure accurate calculations.
- 3. Apply advanced functions: Use the built-in formula builder under the 'Formulas' tab to easily insert and configure your COUNTIFS and IFS logic.
- 4. Save and share: Save your completed tracker as an .xlsx file, ensuring seamless compatibility if you need to share it with management or HR teams.

Frequently Asked Questions
Why is my COUNTIFS formula returning zero for my date range?
This commonly occurs if the imported or entered dates are being read as text rather than numerical date values. Select your date column, right-click, choose 'Format Cells', and ensure a standard 'Date' format is applied. You may need to double-click the cell and press Enter to refresh the format.
Can I track absences instead of tardies using this same rolling method?
Yes. You can add an 'Event Type' column (e.g., Column C) to distinguish between tardies and absences. Then, add an extra criteria pair to your COUNTIFS formula: =COUNTIFS(B:B, ">="&B2-30, B:B, "<="&B2, C:C, "Absence").
How do I visually highlight employees who have reached the termination threshold?
You can use Conditional Formatting for this. Select the 'Warning Status' column, go to the 'Home' tab, click 'Conditional Formatting', select 'Highlight Cells Rules', then 'Equal To'. Type 'Termination' and choose a red fill color to make critical warnings stand out.




