Function Problems

How to Track Tardies in Rolling 30-Day and 90-Day Periods in Spreadsheet

Natalie TaylorNatalie Taylor Oct 16, 2026 869 views

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.

How to Track Employee Tardies in Rolling 30-Day and 90-Day Periods
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.
Before you start

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.

Solution 1Recommended

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.

1
Set up your data columns

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'.

2
Calculate 30-day rolling tardies

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.

3
Calculate 90-day occurrences

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).

4
Assign automated warning thresholds

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.

Formula Automation: Once these formulas are set, the tracking becomes fully automated. Every time a new tardy date is added, the warning status will update instantly based on the current rolling periods.
Effortless Attendance Tracking

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. 1. Create a new tracker: Open WPS Spreadsheet and click 'Blank' to create a new workbook for your attendance data.
  2. 2. Format dates correctly: Highlight your date column, right-click, select 'Format Cells', and apply the proper 'Date' formatting to ensure accurate calculations.
  3. 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. 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.
Fully compatible with Microsoft Excel formulas and .xlsx file formats.Built-in advanced date functions for precise rolling period calculations.Fast processing power for handling large enterprise datasets seamlessly.Free to use with a familiar, intuitive user interface.
microsoft office alternative - wps office

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.