How to join on dates and date ranges

Why a date-range join is different

A normal join matches equal values: order 1001 in one sheet, order 1001 in the other. A date-range join matches a single date to the period that contains it. Typical cases are a price list with valid-from and valid-to dates, exchange rates that change on given days, commission plans by quarter, staff rotas, and a fiscal calendar where week 14 runs from 31 March to 6 April.

The equal-values approach in joining two spreadsheets on a common column won't find anything here, because 3 April doesn't appear in the price table at all. You need a lookup that tests start <= date <= end.

Excel: one value per date with SUMIFS or FILTER

If each SKU has exactly one valid row on any day, SUMIFS returns that row's price, because only one row passes all three tests. With the sales date in A2 and SKU in B2:

=SUMIFS(Prices!D:D, Prices!A:A, B2, Prices!B:B, "<="&A2, Prices!C:C, ">="&A2)

SUMIFS is quick and works in every modern Excel and in Google Sheets, but it hides problems: an overlap adds two prices together and a gap returns 0. In Microsoft 365 and Google Sheets, FILTER returns the matching row itself, so an overlap shows up as a spill of two values and a gap shows your own message:

=FILTER(Prices!D:D, (Prices!A:A=B2) * (Prices!B:B<=A2) * (Prices!C:C>=A2), "no price")

Dates must be real dates on both sides. If one sheet holds text such as “03/04/2026”, convert it first (DATEVALUE, or Text to Columns), otherwise the comparisons silently fail.

Excel: periods with only a start date

Many tables list only the date each rate starts, with the next row implying the end. Use XLOOKUP with match mode -1 (exact match or next smaller item) on the start date. For a single series, such as an exchange rate table with dates in A and rates in B:

=XLOOKUP(A2, Rates!A:A, Rates!B:B, "before first rate", -1)

For several SKUs, restrict the search to the SKU first: =XLOOKUP(A2, FILTER(Prices!B:B, Prices!A:A=B2), FILTER(Prices!D:D, Prices!A:A=B2), , -1). XLOOKUP does not need sorted data for match mode -1, but VLOOKUP with TRUE does — see approximate and closest-match lookups for the details.

Fiscal weeks and custom periods

A fiscal calendar is the same pattern with no product key. Keep a Calendar sheet with Week, Start and End, then =XLOOKUP(A2, Calendar!B:B, Calendar!A:A, , -1) returns the fiscal week for any date. Totals per period work the other way round: =SUMIFS(Sales!E:E, Sales!A:A, ">="&B2, Sales!A:A, "<="&C2) adds up sales between a period's start and end.

SQL: BETWEEN joins

In SQL the range becomes the join condition. BETWEEN is inclusive at both ends, so make sure one period ends the day before the next starts:

SELECT s.date, s.sku, s.qty, p.unit_price, s.qty * p.unit_price AS line_value
FROM sales s
LEFT JOIN prices p
  ON p.sku = s.sku
 AND s.date BETWEEN p.valid_from AND p.valid_to;

If timestamps are involved, use a half-open range instead (s.ts >= p.valid_from AND s.ts < p.next_from) so that a sale at 14:30 on the last day still matches. A LEFT JOIN keeps sales with no price, which you can list with WHERE p.sku IS NULL. Analistable runs exactly this kind of join in DuckDB inside your browser when you ask a question like “value each sale at the price valid on the day”.

Check the period table before you trust the result

• Overlaps: two rows for the same key cover the same day. Duplicates appear in a SQL join and inflate SUMIFS totals.
• Gaps: a day covered by no row. The SQL LEFT JOIN returns NULL; SUMIFS returns 0, which looks like a free item.
• Open-ended rows: a blank valid-to date. Fill it with a far-future date such as 9999-12-31, or use COALESCE(valid_to, DATE '9999-12-31') in SQL.
• Time zones: transactions exported in UTC may fall on a different calendar day from your local period boundary.

-- Overlapping periods for the same SKU
SELECT a.sku, a.valid_from, a.valid_to, b.valid_from AS overlaps_from
FROM prices a JOIN prices b
  ON a.sku = b.sku AND a.valid_from < b.valid_from
 AND b.valid_from <= a.valid_to;

Row counts are the quickest sanity check: a date-range LEFT JOIN should return exactly as many rows as the sales table. More rows means overlaps. For other join problems, see the join spreadsheets guide.

Worked steps for a monthly price check

1. Put the price history on its own sheet with SKU, Valid from, Valid to and Unit price, one row per SKU per period.
2. Fill blank Valid to cells with 31/12/9999 so the current price is found.
3. Run the overlap check below, or sort by SKU and Valid from and eyeball that each row starts the day after the previous one ends.
4. Add the SUMIFS or FILTER column to the sales sheet and count zeros or "no price" results: each one is a gap to fix, not a free sale.
5. Total the line values and compare with the invoiced total for the month. A difference usually points to a price change that was entered late.

The same routine works for commission rates by rep, VAT rates by date or standard costs by period: only the key column changes.

Frequently asked questions

How do I match a date to a fiscal week?

Keep a calendar table with each week's start date and use XLOOKUP with match mode -1 on the start date, or SQL BETWEEN start AND end.

How do I look up a value between two dates in Excel?

Use SUMIFS with two date conditions, for example =SUMIFS(Price, SKU, B2, From, "<="&A2, To, ">="&A2), or FILTER with the same tests in Microsoft 365 and Google Sheets.

Can VLOOKUP match a date range?

Only through approximate match on the start date, which requires the start dates to be sorted ascending and works for one series at a time. XLOOKUP with match mode -1 or FILTER is easier.

Is SQL BETWEEN inclusive?

Yes. BETWEEN a AND b includes both a and b, so adjacent periods must not share a boundary day. For timestamps use >= start AND < next start.

Why does my date-range join return more rows than I started with?

At least two periods overlap for the same key, so some transactions match twice. Find and fix the overlapping rows in the period table.

Related guides

• How to join two spreadsheets on a common column
• How to join on the closest match with an approximate lookup
• How to join spreadsheets on a common column
• How to join two lists on email addresses safely
• How to join two tables on two columns at once