YEAR

You test the cut-off around year end 31 December 2025. In the XAF export every entry must belong to the year of its posting date, and reading the year off by eye goes wrong around New Year. You add a column with the year of each entry date.

Formula in C2

=YEAR(B2)

=YEAR(B2)

Result: 20252025

Watch out: If column C shows a date such as 17-07-1905, the cell carries a date format. The value 2025 is correct; set the format to General.

Your client Veldhuis Installatie BV has a broken financial year from 1 July to 30 June, named after the year in which it ends, so 1 July 2025 belongs to financial year 2026. For the cut-off test you give every journal line from the XAF its financial year. The shift in months sits in E1, and the formula is copied down column C.

Formula in C2

=YEAR(EDATE(A2, $E$1))

=YEAR(EDATE(A2; $E$1))

Result: 20262026

Watch out: If the financial year is named after the year in which it starts (July 2025 is financial year 2025), the shift is -6, not 6. Test with a July date and a June date before copying down.

=YEAR(A2) + (MONTH(A2) >= 7)

=YEAR(A2) + (MONTH(A2) >= 7)

The XAF export of account 8000 Omzet holds lines from three calendar years. You type the audit year into F1 and want the total of that year in F2, to reconcile with the trial balance without a helper column with the year of each line.

Formula in F2

=SUM((YEAR(A2:A8) = F1) * C2:C8)

=SUM((YEAR(A2:A8) = F1) * C2:C8)

Result: €65,100.00€ 65.100,00

Watch out: If F1 holds the text 2025 (typed with a leading apostrophe or imported), the number 2025 is never equal to the text 2025. Every comparison is FALSE and the total is 0, with no error.

=SUMIFS(C2:C8, A2:A8, ">=" & DATE(F1, 1, 1), A2:A8, "<" & DATE(F1 + 1, 1, 1))

=SUMIFS(C2:C8; A2:A8; ">=" & DATE(F1; 1; 1); A2:A8; "<" & DATE(F1 + 1; 1; 1))

For the analytical review of revenue you compare the sales booked so far this year with the same period of last year. The interim date sits in E1, not TODAY, so the working paper stays reproducible.

Comparing a part of this year with a full prior year is the classic error, and the formula must also survive an interim date of 29 February.

Formula in G1

=LET(
  asOf, E1,
  y, YEAR(asOf),
  ytd, SUMIFS(B2:B9, A2:A9, ">=" & DATE(y, 1, 1), A2:A9, "<=" & asOf),
  prior, SUMIFS(B2:B9, A2:A9, ">=" & DATE(y - 1, 1, 1), A2:A9, "<=" & EDATE(asOf, -12)),
  ytd / prior - 1
)

=LET(
  asOf; E1;
  y; YEAR(asOf);
  ytd; SUMIFS(B2:B9; A2:A9; ">=" & DATE(y; 1; 1); A2:A9; "<=" & asOf);
  prior; SUMIFS(B2:B9; A2:A9; ">=" & DATE(y - 1; 1; 1); A2:A9; "<=" & EDATE(asOf; -12));
  ytd / prior - 1
)

Result: 16.8%16,8%

Watch out: The mirror-image mistake is computing prior as the whole of last year, for example SUM((YEAR(A2:A9) = y - 1) * B2:B9). It returns 172500, and growth becomes 146000 / 172500 - 1 = -15.4%: an apparent decline that is really a partial year set against a full one.

=LET(
  asOf, E1,
  y, YEAR(asOf),
  ytd, SUMIFS(B2:B9, A2:A9, ">=" & DATE(y, 1, 1), A2:A9, "<=" & asOf),
  prior, SUMIFS(B2:B9, A2:A9, ">=" & DATE(y - 1, 1, 1), A2:A9, "<=" & EDATE(asOf, -12)),
  ytd - prior
)

=LET(
  asOf; E1;
  y; YEAR(asOf);
  ytd; SUMIFS(B2:B9; A2:A9; ">=" & DATE(y; 1; 1); A2:A9; "<=" & asOf);
  prior; SUMIFS(B2:B9; A2:A9; ">=" & DATE(y - 1; 1; 1); A2:A9; "<=" & EDATE(asOf; -12));
  ytd - prior
)