Extract Nonblank Values and Dates from the Last 365 Days in Excel
Question details
Extract nonblank values and their corresponding dates when the dates fall within the previous 365 days.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains dates and associated values, and the user wants an automatically updated list of recent nonblank entries.
- Observed behavior
- The desired output includes only nonblank values with corresponding dates from TODAY()-365 through today.
Check that the dates are stored as Excel dates rather than text. The examples use dates in A2:A100 and their corresponding values in B2:B100; adjust the ranges to fit your worksheet.
Use FILTER to extract recent nonblank values and dates
Use this method in an Excel version that supports dynamic arrays, such as Microsoft 365 or Excel 2021.
Select an empty cell such as D2, with room for the results to spill into two columns.
In D2, enter =FILTER(A2:B100,(B2:B100<>"")*ISNUMBER(A2:A100)*(A2:A100>=TODAY()-365)*(A2:A100<TODAY()+1),"No matches") and press Enter.
Select the date column in the output and choose Home > Number Format > Short Date if the dates appear as numbers.

Use an array formula in older Excel versions
Use INDEX and SMALL when FILTER is unavailable.
Extract recent values and dates in WPS Spreadsheets
Use an array formula in WPS Spreadsheets to list nonblank values alongside their dates within the selected date window.
- 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook. Check that dates are in A2:A100 and values are in B2:B100, or adjust the formula ranges.
- 2. Enter the date formula: Select D2, enter the date formula from the older Excel solution above, and confirm it with Ctrl+Shift+Enter.
- 3. Enter the value formula: Select E2, enter the value formula from the older Excel solution, and confirm it with Ctrl+Shift+Enter.
- 4. Fill and format the results: Drag the fill handle from D2:E2 down to show all matches. Select the output dates in column D and apply a date format from the Home tab.

Frequently Asked Questions
How do I exclude future dates from the results?
Add a condition such as date_range<TODAY()+1 alongside date_range>=TODAY()-365. The upper bound includes times on today's date but excludes future dates.
Why does FILTER show a spill error?
Clear any cells blocking the output area next to or below the formula. FILTER needs empty cells to display both the dates and values.
Why are some dates missing from the results?
Check that the source cells contain real date values rather than text and that the corresponding value cells are not blank. The examples also exclude dates outside the specified window.
Do I need Ctrl+Shift+Enter for FILTER?
No. Enter the FILTER formula with Enter in a supported Excel version. In older Excel versions, confirm the INDEX and SMALL array formulas with Ctrl+Shift+Enter.
Does TODAY()-365 include exactly 365 calendar dates?
With both endpoints included, TODAY()-365 through today covers 366 calendar dates. Use TODAY()-364 as the lower bound to include exactly 365 calendar dates counting today.




