Calculation Issues

How to Calculate Rolling 12-Month Attendance Totals in Excel

Phi Hung Vo Oct 23, 2026 868 views

Question details

The user needs a method to calculate employee leave occurrences over a rolling 12-month period rather than a static calendar-year total.

How to Calculate Rolling 12-Month Attendance Totals in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Tracking employee attendance and managing leave point systems where absences expire exactly 12 months after the occurrence date.
Observed behavior: The current spreadsheet calculates totals based on a fixed calendar year, failing to dynamically update as the 12-month window shifts forward.

Before you start

Ensure your attendance spreadsheet has a dedicated column for leave dates, and verify that these dates are formatted as proper Date values in Excel rather than plain text.

Solution 1Recommended

Use COUNTIFS and EDATE for a Dynamic Rolling 12-Month Total

Implement a formula using COUNTIFS alongside TODAY() and EDATE to automatically count occurrences from the past year up to the current date.

A rolling period cannot be calculated reliably by just finding the earliest leave date. By combining the TODAY() function to identify the current date and the EDATE function to subtract exactly 12 months, you create a dynamic window that updates automatically every day.

Select the target cell

Click on the cell where you want the total rolling 12-month attendance count to be displayed.

Enter the COUNTIFS formula

Type the formula =COUNTIFS(A:A, ">="&EDATE(TODAY(),-12), A:A, "<="&TODAY()) into the formula bar, replacing 'A:A' with the specific column range that contains your employee leave dates.

Apply and verify

Press Enter to execute the formula. The cell will now output the total number of leave occurrences strictly within the trailing 12-month window.

Use COUNTIFS and EDATE for a Dynamic Rolling 12-Month Total

Tracking Multiple Employees: If tracking multiple employees in a single dataset, add another criteria range for the employee name. For example: =COUNTIFS(A:A, ">="&EDATE(TODAY(),-12), A:A, "<="&TODAY(), B:B, "Employee Name").

WPS Spreadsheet Solution

Easily Track Rolling Attendance with WPS Spreadsheet

WPS Spreadsheet fully supports advanced date functions like COUNTIFS, EDATE, and TODAY(), allowing you to build dynamic rolling 12-month attendance trackers with zero hassle.

1. Open your attendance tracker: Launch WPS Spreadsheet and open your existing employee attendance workbook.
2. Select the calculation cell: Click on the cell designated for the rolling 12-month total.
3. Input the dynamic formula: Enter the COUNTIFS formula using EDATE and TODAY() exactly as you would in Microsoft Excel.
4. Apply to multiple rows: Press Enter to calculate the result, then drag the fill handle down to apply the rolling period formula to the rest of your employees.

100% compatible with Microsoft Excel formulas and date formattingAccess to free, built-in HR and attendance tracking templatesLightweight software that handles complex array calculations smoothly

microsoft office alternative - wps office

Frequently Asked Questions

Can I calculate the rolling 12-month period based on a specific end date instead of today?

Yes. Instead of using TODAY(), replace it with a reference to the cell containing your specific target date. For example: =COUNTIFS(A:A, ">="&EDATE(C2,-12), A:A, "<="&C2), assuming C2 holds your target date.

How do I calculate total hours taken in a rolling 12-month period instead of just the number of occurrences?

To calculate a sum instead of a count, use the SUMIFS function. The syntax would be: =SUMIFS(Hours_Column, Date_Column, ">="&EDATE(TODAY(),-12), Date_Column, "<="&TODAY()).

Why is my rolling 12-month formula returning zero?

This commonly occurs if the leave dates in your column are stored as text rather than actual Excel Date values. Select the column containing your dates, right-click to choose 'Format Cells', and ensure the format is set to Date.

Excel & Data Problems