MONTH

You are testing journal entries in the XAF auditfile of Veldhuis Installatie BV, exported to a sheet. Before you filter on the postings of one month, you add a column with the month number of each posting date.

Formula in D2

=MONTH(B2)

=MONTH(B2)

Result: 1111

Watch out: D4 returns 1 for January 2026, the same as a posting in January 2025 would. Filtering on 1 shows both years unless you also filter on the year.

For the cut-off test around year end 31 December 2025 you total the journal lines per period. The auditfile holds lines from December 2025 and from 2026, and the month number alone would put both Decembers in the same bucket.

You build a key like 202512 that sorts in time order, and copy the formula down column E.

Formula in E2

=YEAR(B2) * 100 + MONTH(B2)

=YEAR(B2) * 100 + MONTH(B2)

Result: 202512202512

Watch out: Joining the parts as text, YEAR(B2) & MONTH(B2), gives 20262 for February and 202610 for October. The keys differ in length and, sorted as text, October comes before February. Multiplying by 100 keeps the month at two digits.

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

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

Your client's fiscal year starts on 1 April, so April to June is the first quarter and January to March the fourth.

For the quarterly analysis of journal entries, each posting needs a quarter label. The start month sits in F1, so the same formula works for another client with a different fiscal year.

Formula in D2

="Q" & ROUNDUP((MOD(MONTH(A2) - $F$1, 12) + 1) / 3, 0)

="Q" & ROUNDUP((MOD(MONTH(A2) - $F$1; 12) + 1) / 3; 0)

Result: Q4

Watch out: Leaving out the + 1 looks harmless and even gives Q4 for February, but it shifts every quarter boundary by a month: July returns Q1 instead of Q2, and April returns Q0.

=CHOOSE(MONTH(A2), 4, 4, 4, 1, 1, 1, 2, 2, 2, 3, 3, 3)

=CHOOSE(MONTH(A2); 4; 4; 4; 1; 1; 1; 2; 2; 2; 3; 3; 3)

You recalculate depreciation from the fixed asset register: an asset is depreciated for every calendar month in which it is in use, and any day in a month counts as the whole month. Assets still in use have no disposal date, one disposal date was keyed in before the purchase date, and the count must be exact. Assets still in use are counted up to the balance sheet date in G1.

Formula in D2

=LET(
  start, B2,
  finish, IF(ISBLANK(C2), $G$1, C2),
  startIdx, YEAR(start) * 12 + MONTH(start),
  finishIdx, YEAR(finish) * 12 + MONTH(finish),
  MAX(0, finishIdx - startIdx + 1)
)

=LET(
  start; B2;
  finish; IF(ISBLANK(C2); $G$1; C2);
  startIdx; YEAR(start) * 12 + MONTH(start);
  finishIdx; YEAR(finish) * 12 + MONTH(finish);
  MAX(0; finishIdx - startIdx + 1)
)

Result: 55

Watch out: The mirror-image mistake is dropping the + 1. The formula still looks sensible and returns 4 for A-101, but it counts the gaps between months instead of the months in use, so every recalculated charge is one month short.