DATE

The bank journal (BNK) arrives from the client's system with the posting date as three separate numbers: year, month and day. For the cut-off test around 31 December 2025 you need one proper date per entry in column E, so you can sort and filter on it.

Formula in E2

=DATE(B2, C2, D2)

=DATE(B2; C2; D2)

Result: 31 Dec 2025

Watch out: If a future export writes the year as 25, E2 becomes Dec 31, 1925. Nothing warns you, so check the year column after each import.

The fixed asset register (activastaat) lists the start year and start month of depreciation per asset. You recalculate from which date each asset is fully depreciated: the first day of the month after the last depreciation charge.

The useful life in months sits in F1, so you can change it per asset class. Assets that start late in the year must land in a later year.

Formula in D2

=DATE(B2, C2 + $F$1, 1)

=DATE(B2; C2 + $F$1; 1)

Result: 1 Apr 2026

Watch out: Without the $ signs, D3 would read F2, which is empty and counts as 0, and show 1 September 2023: a plausible date that is wrong.

=DATE(B2, C2 + $F$1, 0)

=DATE(B2; C2 + $F$1; 0)

For the payables cut-off test you recalculate the due date of purchase invoices (journal INK). The supplier terms count from the end of the invoice month, not from the invoice date: 30 days after end of month, and 14 days for one supplier.

You want to know which invoices fall due after 31 December 2025, including an invoice from December and one from the leap-year February of 2024.

Formula in D2

=DATE(YEAR(B2), MONTH(B2) + 1, 0) + C2

=DATE(YEAR(B2); MONTH(B2) + 1; 0) + C2

Result: 30 Dec 2025

Watch out: Writing the month end as DATE(YEAR(B2), MONTH(B2), 30) looks right for November but is silently wrong for February 2024: day 30 does not exist, so it returns 1 March 2024 and the due date shifts by one day.

=EOMONTH(B2, 0) + C2

=EOMONTH(B2; 0) + C2

=DATE(YEAR(B2), MONTH(B2), 1)

=DATE(YEAR(B2); MONTH(B2); 1)

Your client closes its books on 30 June, so its financial year runs from 1 July to 30 June. For the interim procedures and the cut-off tests you want the start and end date of each quarter as a small spilled table, driven by two settings, so the same formula serves next year and other clients with a broken financial year.

Formula in D2

=LET(
  fyYear, B2,
  fyMonth, B3,
  q, SEQUENCE(4),
  DATE(fyYear, fyMonth + 3 * (q - 1) + {0,3}, {1,0})
)

=LET(
  fyYear; B2;
  fyMonth; B3;
  q; SEQUENCE(4);
  DATE(fyYear; fyMonth + 3 * (q - 1) + {0\3}; {1\0})
)

Result: 1 Jul 2025 | 30 Sep 2025 / 1 Oct 2025 | 31 Dec 2025 / 1 Jan 2026 | 31 Mar 2026 / 1 Apr 2026 | 30 Jun 20261 Jul 2025 | 30 Sep 2025 / 1 Oct 2025 | 31 Dec 2025 / 1 Jan 2026 | 31 Mar 2026 / 1 Apr 2026 | 30 Jun 2026

Watch out: Swapping the day constant to {0,1} looks almost the same but gives the day before each quarter start and the day after each quarter end: 30 June 2025 to 1 October 2025 in row 1. Check that row 1 starts on the first of fyMonth.

=LET(
  q, SEQUENCE(4),
  DATE(B2, B3 + 3 * q, 0)
)

=LET(
  q; SEQUENCE(4);
  DATE(B2; B3 + 3 * q; 0)
)