Dynamic Arrays
Filter dates expiring in the next 30 days (sorted renewal alert queue)
Extract contracts, certificates, or subscriptions expiring inside [AsOfDate, AsOfDate + 30] and sort them soonest-first with SORT + FILTER.
Bound BOTH sides of the alert window so past-due and far-future rows stay separate
Filter between the as-of date D2 and D2+E2, inclusive, then sort by the expiry date. With D2 set to 1 June and E2 to 30, the window includes 1 June through 1 July: today plus the following 30 dates.
Use real date values without time components and a nonnegative whole-number window. If you need exactly 30 calendar dates including today, use E2=29. Dates with times need an exclusive upper bound at the following midnight.
This is a date queue, not an automatic email alert. Annual birthdays require computing the next occurrence first, including an explicit leap-day policy.
The amounts are illustrative annual contract costs. They do not establish accounting revenue or annual cost for the supplier.
| Vendor / Cert (A) | ExpiryDate (B) | annual cost (C) | Status as of 2026-06-01 (30d Window) |
|---|---|---|---|
| SSL Wildcard | 2026-05-20 | $400 | Excluded (Already Expired < Jun 1) |
| AWS Reserved | 2026-06-18 | $24,000 | Included (#2 — 17 days out) |
| Snowflake | 2026-06-05 | $18,000 | Included (#1 Urgent — 4 days out!) |
| Okta SSO | 2026-08-15 | $9,000 | Excluded (> Jul 1) |
Sorted upcoming-renewal list and expiring-annual cost sum formulas
SORT(..., 2, 1) sorts the extracted [Vendor, ExpiryDate] pairs ascending by ExpiryDate before slicing names or displaying.
=IF(COUNTIFS(B2:B5,">="&D2,B2:B5,"<="&(D2+E2))=0,"No renewals",TEXTJOIN(", ", TRUE, CHOOSECOLS(SORT(FILTER(A2:B5, (B2:B5 >= D2) * (B2:B5 <= D2 + E2)), 2, 1), 1)))Total contract annual cost up for renewal in the next N days
Uses SUMIFS over the exact same [D2, D2 + E2] window.
=SUMIFS(C2:C5, B2:B5, ">=" & D2, B2:B5, "<=" & (D2 + E2))Try the example
Use the original values or change them to test your understanding.