Lookup & Reference
Get first and last entry by month and year (FILTER, TAKE, DATE & EOMONTH)
Return the first and last source-row balances within a selected calendar month, including timestamps on its final day.
Use an inclusive start and exclusive next-month boundary
Include timestamps at or after DATE(year,month,1) and strictly before EOMONTH(start,0)+1. A less-than-or-equal comparison with the last date alone would exclude times after midnight on that last day.
TAKE returns the first or last matching row in source order. It does not sort timestamps. Sort records chronologically first if you mean earliest and latest; repeated timestamps retain their source ordering.
These are recorded balances inside the month, not necessarily the accounting opening and closing balances. A first March observation on March 2 does not establish the balance at the start of March 1. The difference between selected observations is not automatically net cash flow.
Require complete numeric date-time and balance columns. This exercise restricts years to 1901–9998 and months to whole numbers 1–12, avoiding DATE normalization of invalid selectors. Zero and negative balances remain valid.
| Ledger Date (A) | Cash Balance (B) | In March 2026 Window? | Monthly Boundary Role |
|---|---|---|---|
| 2026-02-25 | $950 | No (< Mar 1) | Prior month |
| 2026-03-02 | $1,200 | Yes | First entry -> TAKE(..., 1) = 1,200 |
| 2026-03-15 | $1,340 | Yes | Mid-month activity |
| 2026-03-29 | $1,580 | Yes | Last entry -> TAKE(..., -1) = 1,580 |
| 2026-04-03 | $1,610 | No (> Mar 31) | Next month |
First recorded balance in the selected month
Returns "No Data" cleanly if the target month has zero recorded entries.
=IF(OR(COUNT(D2:E2)<>2,COUNT(A2:B6)<>10),"Invalid inputs",IF(OR(D2<>INT(D2),E2<>INT(E2),D2<1901,D2>9998,E2<1,E2>12,MIN(A2:A6)<DATE(1901,1,1),MAX(A2:A6)>=DATE(9999,1,1)),"Invalid inputs",TAKE(FILTER(B2:B6,(A2:A6>=DATE(D2,E2,1))*(A2:A6<EOMONTH(DATE(D2,E2,1),0)+1),"No Data"),1)))Last recorded balance in the selected month
Passing -1 to TAKE grabs the final row matching the month window.
=IF(OR(COUNT(D2:E2)<>2,COUNT(A2:B6)<>10),"Invalid inputs",IF(OR(D2<>INT(D2),E2<>INT(E2),D2<1901,D2>9998,E2<1,E2>12,MIN(A2:A6)<DATE(1901,1,1),MAX(A2:A6)>=DATE(9999,1,1)),"Invalid inputs",TAKE(FILTER(B2:B6,(A2:A6>=DATE(D2,E2,1))*(A2:A6<EOMONTH(DATE(D2,E2,1),0)+1),"No Data"),-1)))Try the example
Use the original values or change them to test your understanding.