Function Problems

How to Count Absences and Shade Expired Rows in Excel

Camila Milosovich Oct 21, 2026 869 views

Question details

Calculate active absences from the past 12 months and visually gray out older, expired absence records.

How to Count Absences and Shade Expired Rows in Excel

Product: Excel
Device & OS: not provided
Scenario: Tracking employee attendance dynamically based on a rolling 12-month period.
Observed behavior: Needs dynamic formulas to calculate valid absences and a specific conditional formatting rule to highlight or hide records older than 12 months automatically.

Before you start

Ensure your absence dates are formatted as valid Excel dates rather than text, and that your dataset is organized into clear columns for Dates, Absence Types, and Employee Identifiers.

Solution 1Recommended

Use COUNTIFS and Conditional Formatting to Track Rolling Absences

Combine the COUNTIFS function with EDATE to count records within the last year, and use a formula-based conditional formatting rule to shade older rows.

The EDATE function calculates a date a specific number of months in the past or future. By combining EDATE with TODAY(), you can create a dynamic 12-month rolling window.

To properly shade an entire row instead of just a single cell, you must use a mixed cell reference (e.g., $A2) in your conditional formatting rule.

Count unexpired absences

Select a blank cell and enter the formula =COUNTIFS($A$2:$A$1000,">"&EDATE(TODAY(),-12),$C$2:$C$1000,1). This counts records where the date in column A is within the last 12 months, and column C matches the specific employee.

Filter absences by type (Optional)

To count only specific absence types, use the formula =COUNTIFS($A$2:$A$1000,">"&EDATE(TODAY(),-12),$B$2:$B$1000,"Unexcused"). Adjust "Unexcused" to match the exact text in your tracking sheet.

Open the Conditional Formatting menu

Select your entire dataset range (e.g., A2:D1000). Navigate to the Home tab on the ribbon, click on Conditional Formatting, and select New Rule.

Apply the shading rule for expired rows

Choose 'Use a formula to determine which cells to format'. Enter the formula =$A2<=EDATE(TODAY(),-12). Click the Format button, select the Fill tab, pick a gray color (or change the Font color to light gray), and click OK to apply the rule.

Use COUNTIFS and Conditional Formatting to Track Rolling Absences

Dynamic Updates: Because this solution relies on the TODAY() function, the 12-month window will automatically update every time you open the workbook.

Track Absences Efficiently

Easily Manage Attendance with WPS Spreadsheet

WPS Spreadsheet fully supports advanced mathematical functions like COUNTIFS, EDATE, and formula-based conditional formatting. You can build powerful, dynamic attendance trackers that instantly update to reflect rolling 12-month periods without any extra software.

1. Open your attendance sheet: Launch WPS Spreadsheet and open the file containing your employee absence records.
2. Calculate rolling absences: Click an empty cell and input your COUNTIFS formula using EDATE(TODAY(),-12) to dynamically sum the past 12 months.
3. Select the data range: Highlight the rows containing your absence data that you want to apply the gray shading to.
4. Create a formatting rule: Go to Home > Conditional Formatting > New Rule, select 'Use a formula', and input =$A2<=EDATE(TODAY(),-12).
5. Apply gray shading: Click Format, choose a gray background or font color, and click OK to instantly gray out expired records.

100% compatible with Microsoft Excel formulas like EDATE and COUNTIFSIntuitive conditional formatting interface for data visualizationFree to use with a lightweight, fast-loading applicationSeamless cross-platform synchronization for managing attendance on the go

microsoft office alternative - wps office

Frequently Asked Questions

How do I change the rolling period from 12 months to 6 months?

To change the rolling time frame, simply modify the number in the EDATE formula. Change -12 to -6 (e.g., EDATE(TODAY(), -6)) to track absences over a 6-month period instead.

Why is only the first cell in my row turning gray?

This happens when the formula uses a relative reference like A2 instead of a mixed reference. Ensure you put a dollar sign before the column letter (e.g., =$A2) in the conditional formatting rule to lock the column while allowing the row number to adjust.

Can I hide the expired rows completely instead of shading them?

Conditional formatting cannot hide rows or change row heights. To hide expired rows, you would need to use Excel's Filter feature by filtering out dates older than the past 12 months, or write a custom VBA macro to automatically hide those rows.

Does the TODAY() function slow down my spreadsheet?

TODAY() is a volatile function, meaning it recalculates every time any change is made to the worksheet. While a few TODAY() functions won't impact performance, using them in conditional formatting across tens of thousands of rows might cause slight lag on older devices.

Excel & Data Problems