Function Problems

Extract Nonblank Values and Dates from the Last 365 Days in Excel

Tauseeq MagsiTauseeq Magsi Nov 14, 2026 870 views

Question details

Extract nonblank values and their corresponding dates when the dates fall within the previous 365 days.

Extract Nonblank Values and Dates from the Last 365 Days in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Choose an output cell

Select an empty cell such as D2, with room for the results to spill into two columns.

2
Enter the FILTER formula

In D2, enter =FILTER(A2:B100,(B2:B100<>"")*ISNUMBER(A2:A100)*(A2:A100>=TODAY()-365)*(A2:A100<TODAY()+1),"No matches") and press Enter.

3
Format the dates

Select the date column in the output and choose Home > Number Format > Short Date if the dates appear as numbers.

Use FILTER to extract recent nonblank values and dates
Date boundary: The formula includes dates from TODAY()-365 through today, including entries with a time on today's date. To include exactly 365 calendar dates including today, change TODAY()-365 to TODAY()-364.
WPS Spreadsheets

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. 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. 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. 3. Enter the value formula: Select E2, enter the value formula from the older Excel solution, and confirm it with Ctrl+Shift+Enter.
  4. 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.
Extract paired dates and values using a TODAY-based conditionOpen and edit Microsoft Excel XLS and XLSX filesUse familiar spreadsheet formulas and formatting toolsWork with a free, easy-to-use office suite
microsoft office alternative - wps office

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.