Function Problems

How to Sum Days Off Within the Previous 365 Days in Excel

Maira Mehtab Oct 16, 2026 868 views

Question details

The user needs to calculate the total number of days taken off within a rolling 365-day period based on recorded start dates.

How to Sum Days Off Within the Previous 365 Days in Excel

Product: Excel
Device & OS: not provided
Scenario: Tracking employee attendance or personal leave dynamically over a rolling 12-month window.
Observed behavior: Requires a dynamic formula to sum values in a specific column only when the corresponding date is between today and exactly 365 days ago.

Before you start

Ensure that the start dates in your tracking column are formatted as actual date values in Excel, not text, so the formula can correctly evaluate the timeline.

Solution 1Recommended

Use the SUMIFS and TODAY Functions

Combine SUMIFS with TODAY to dynamically calculate the sum of days off for a rolling 365-day period.

The SUMIFS function allows you to add up values based on multiple conditions. By pairing it with the TODAY function, you can create a dynamic date range that continually evaluates the previous 365 days without requiring manual updates.

Select the target cell

Click on an empty cell where you want the total sum of days off to appear.

Enter the SUMIFS formula

Type the formula =SUMIFS(D:D,A:A,">="&TODAY()-365,A:A,"<="&TODAY()) assuming your dates are in column A and the days off values are in column D.

Execute the formula

Press Enter to calculate the result. The formula will automatically update each day to accurately reflect the current 365-day window.

Date Format Verification: If the formula returns 0 unexpectedly, verify that the dates in column A are recognized by Excel. Use the DATEVALUE function to convert text-formatted dates into valid serial numbers if necessary.

Easily Calculate Data in WPS Office

Manage Date Ranges and Formulas with WPS Spreadsheet

You can effortlessly manage time-off tracking and apply dynamic formulas like SUMIFS and TODAY using WPS Spreadsheet, a fully compatible and lightweight spreadsheet tool.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open the file where you track days off.
2. Select the total cell: Click on the specific cell where you want to display the 365-day total.
3. Input the dynamic formula: Type =SUMIFS(D:D,A:A,">="&TODAY()-365,A:A,"<="&TODAY()) and press Enter to instantly calculate the sum.
4. Save as XLSX: Save your workbook in the standard .xlsx format to ensure seamless sharing and ongoing compatibility.

Fully compatible with Microsoft Excel formulas and date formatting conventionsIntuitive and user-friendly interface for managing complex datasetsFree to download and highly efficient for everyday office tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does my SUMIFS formula return an error or zero?

This usually happens when dates are stored as text. Select your date column, go to the Data tab, and use the Text to Columns feature to convert them, or wrap your cell references in the DATEVALUE function.

How do I sum days off for a specific calendar year instead of a rolling 365 days?

You can modify the SUMIFS criteria to reference the exact start and end dates of the target year. For example, use =SUMIFS(D:D, A:A, ">=1/1/2023", A:A, "<=12/31/2023").

Can I use this formula to track other rolling periods, like 30 days?

Yes, simply change the -365 in the formula to your desired timeframe. For instance, using TODAY()-30 will calculate the sum for the last 30 days.

Excel & Data Problems