Calculation Issues

How to Create a Rolling 12-Month Absence Calculator in Excel

Maira Mehtab Sep 28, 2026 869 views

Question details

The user needs to create a spreadsheet to monitor employee absences and automatically calculate the total amount of leave taken over a continuously updating 12-month period.

How to Create a Rolling 12-Month Absence Calculator in Excel

Product: Excel
Device & OS: not provided
Scenario: Tracking staff attendance and dynamically calculating accumulated absence totals over the past year based on the current date.
Observed behavior: The goal is to establish a rolling calculation that updates automatically as time passes, without needing manual date adjustments each month.

Before you start

Gather a list of your employees and determine the specific types of absences you wish to track (e.g., sick leave, annual leave). Ensure your spreadsheet software is set to calculate formulas automatically so the rolling dates update every day.

Solution 1Recommended

Use and Modify a Pre-built Absence Tracking Template

The fastest way to achieve this is by starting with a pre-formatted template and adjusting its formulas to support a rolling 12-month period.

Using a template saves you from setting up formatting, calendars, and basic data entry layouts from scratch. You only need to inject the dynamic rolling date logic.

Search for a template

Open your spreadsheet software, click on 'File' > 'New', and search for 'Absence Tracker' or 'Employee Attendance' in the template library.

Set up the data logs

In the tracking sheet, create or locate columns for 'Employee Name', 'Absence Date', and 'Duration' (in days or hours).

Apply dynamic date criteria

Locate the summary dashboard where totals are calculated. Modify the summing formula to include a date check using the TODAY() and EDATE() functions to capture only the last 12 months.

Use and Modify a Pre-built Absence Tracking Template

Time Saver: Templates usually come with built-in color-coding and charts, making your final absence calculator much more visually appealing for management reporting.

Solution 2

Build a Custom Rolling Calculator using SUMIFS

If you need specific data structures, you can build a customized tracking system from scratch using the SUMIFS formula combined with EDATE.

Calculate Absences Dynamically

Create Your Rolling Absence Tracker in WPS Spreadsheet

WPS Office offers a robust spreadsheet tool with a massive library of free HR and attendance templates, making it incredibly easy to set up complex rolling 12-month calculators.

1. Open WPS Spreadsheet: Launch WPS Office and select Spreadsheet from the main dashboard.
2. Browse HR Templates: Click on 'Templates' on the right sidebar and search for 'Attendance' to download a free tracking sheet.
3. Add the rolling logic: Navigate to the summary cell and implement the =SUMIFS() formula combined with EDATE(TODAY(), -12) to calculate the dynamic 12-month window.
4. Save and share: Save your completed tracker in .xlsx format to ensure HR managers or colleagues can open it on any device without compatibility issues.

Access hundreds of free attendance and HR tracking templates instantlyFull support for advanced dynamic date functions like SUMIFS, TODAY, and EDATESeamless and strict compatibility with Microsoft Excel (.xlsx) file formatsLightweight, fast, and completely free to use for daily business tasks

Frequently Asked Questions

How do I calculate a date exactly 12 months ago in Excel?

You can use the EDATE function. Entering the formula =EDATE(TODAY(), -12) in any cell will automatically return the exact date 12 months prior to the current day. This updates automatically every day.

Can I track different types of absences like sick leave and vacation separately?

Yes. Add an 'Absence Type' column to your data entry sheet. Then, expand your SUMIFS formula to include that column as a condition. For example: =SUMIFS(Days_Range, Dates_Range, ">="&EDATE(TODAY(),-12), Type_Range, "Sick Leave").

Why is my rolling 12-month total not updating when a new day starts?

Your spreadsheet's calculation option might be set to Manual. Go to the Formulas tab, click on Calculation Options, and ensure 'Automatic' is selected. Formulas using TODAY() will recalculate whenever you open the file or make a change.

Excel & Data Problems