---
title: "Filter rows by date in Strata | Oplema"
description: "Learn to filter a CSV to one week of finished hires in Strata, then give each rejected row a reason: other weeks, cancellations, no-shows or no date."
url: "https://oplema.com/learn/export-to-report/filter-rows-by-date"
updated: "2026-11-04"
---

Lesson 6 of 10 · Lab

# Filter rows by date and account for what's left out

Filter keeps the rows that pass a rule and sends the rest to a second output. Kept plus rejected rows must equal the rows that came in, and each rejected row needs a reason.

A rule that combines `status = 'completed'` with a date range keeps finished hires inside it. A row whose date is empty fails it, because an unknown date isn't between anything.

Marit, who owns [fictional Kramerica Bikes](https://oplema.com/learn/export-to-report/first-data-flow), wants Harbour's week of September 14 to 20, finished hires only. Tomas's 321 rows arrive as the last lesson left them, with `hire_on` a date and `amount_eur` a number.

Three hires are dated `see note` in the till, so their `hire_on` is empty. Will the week's rule keep them?

No, they go to False. They're real hires, so the report must account for them.

## Filter the week, then read what fell out

1. This live flow in [Strata](https://oplema.com/strata), Oplema's browser app for data prep, opens with **Filter** selected. Its **Expression** keeps finished hires from September 1 to 30. **OUTPUT TRUE** opens first.
2. Select **Summarize**, the upper block on the right. It adds up the kept rows: 283 hires and a `gross` of 11293.5 (€11,293.50), the price of finished hires, VAT included.
3. Select **Filter**. In **Expression**, double-click `01` in `2026-09-01` and type `14`. Change `30` in `2026-09-30` to `20`, then select the **Filter** block.
4. Select **Summarize** again. It reads 93 hires and a `gross` of 4093.5. `priced` counts the 91 hires that have an amount.
5. Drag from **Filter**'s lower right dot, its False output, to **Sort**'s left dot.
6. Select **Sort**. Set **Column** to `hire_on` and **Direction** to **Descending**.
7. Read **Sort**'s preview of 228 rows and scroll right to `status`. The first 13 are the week's `cancelled` and `no-show` bookings, then September 13 back to 1.
8. Scroll to the bottom. The last three rows have no `hire_on`, and their `hire_date` says `see note`.

In an expression, press Esc to apply it and move on (press it twice if a suggestion list is open).

What the two outputs hold

True keeps 93 hires with a `gross` of €4,093.50. `priced` says 91 because two staff hires are free. False holds 228 rows: 212 from other weeks, the week's 11 cancellations and 2 no-shows, then 3 hires with no date, worth €93.00 gross. 93 + 228 = 321.

## Every rejected row needs a reason

Cancellations and no-shows are out by your rule, as Marit asked. The three undated hires are different: they happened, but no date rule can place them. Report them beside the week's figure: 93 hires, and 3 more with no date, worth €93.00 gross. Next, take VAT off the 93.

See the [Filter](https://oplema.com/docs/strata/prep/filter) and [Sort references](https://oplema.com/docs/strata/prep/sort). For later: [Filtering rows](https://oplema.com/learn/data-preparation/filter-rows) explains why an empty value fails every ordinary comparison.

## Common date filter mistakes

- **Stopping the range a day early.** `BETWEEN` includes both dates, while `hire_on < DATE '2026-09-20'` drops the Sunday.
- **Comparing dates written as text.** As text, `'01/10/2026'` comes before `'14/09/2026'`, so a range on text dates mixes up months.
- **Leaving the False output unread.** Nothing warns you when real rows fall out, so read it or count it.

## Date ranges in SQL and Excel

```
SELECT  *  FROM  harbour
WHERE  status  =  'completed'
  AND  hire_on  BETWEEN  DATE  '2026-09-14'  AND  DATE  '2026-09-20' ;

SELECT  *  FROM  harbour
WHERE  NOT  ( status  =  'completed'
  AND  hire_on  BETWEEN  DATE  '2026-09-14'  AND  DATE  '2026-09-20' )
   OR  hire_on  IS  NULL ;
```

SQL has no False output, so the second query rebuilds it. `IS NULL` is needed because `NOT` of an unknown is still unknown, so the undated hires would vanish from both lists. In Excel, `=FILTER(A2:G322, (G2:G322="completed")*(C2:C322>=DATE(2026,9,14))*(C2:C322<=DATE(2026,9,20)))` returns the kept rows; the rejected ones need a second formula.
