EOMONTH

In the XAF auditfile of Veldhuis Installatie BV, every journal line is booked in the period that ends on the last day of its month. For entry 25-0412 you want a cell with that period end, so you can compare it with the closing date of the period in the trial balance, without looking up how many days December has.

Formula in D3

=EOMONTH(B3, 0)

=EOMONTH(B3; 0)

Result: 31 Dec 2025

Watch out: If D3 shows 46022, the formula is right and only the format is missing. Do not retype the date by hand; set the cell to a date format.

You are testing the payables of Veldhuis Installatie BV against the aging list of creditors (ouderdomsanalyse crediteuren). The suppliers invoice on terms of 30 days after the end of the invoice month, and you want a recalculated due date next to every invoice, copied down the list.

Some invoices are in the list before the date has been keyed in, so a blank invoice date must give a blank due date, not a date in 1900.

Formula in D2

=IF(B2="", "", EOMONTH(B2, 0) + $G$1)

=IF(B2=""; ""; EOMONTH(B2; 0) + $G$1)

Result: 30 Dec 2025

Watch out: Adding 30 to the invoice date instead of to the month end gives 20 Dec 2025 for C2001: a due date 10 days too early that looks perfectly plausible, and it makes an invoice look overdue in your aging analysis.

=IF(B2="", "", EOMONTH(B2, 1))

=IF(B2=""; ""; EOMONTH(B2; 1))

In the fixed asset register (activastaat) of Veldhuis Installatie BV, the first month's depreciation is charged pro rata: an asset is depreciated only for the days from the date it is put into use to the end of that month.

You recalculate the charge for forklift A-102, in use since 18 Sep 2025, at 600 a month, and compare it with the amount the client booked. The formula has to work for months of 28, 30 and 31 days.

Formula in E3

=D3 * (EOMONTH(C3, 0) - C3 + 1) / DAY(EOMONTH(C3, 0))

=D3 * (EOMONTH(C3; 0) - C3 + 1) / DAY(EOMONTH(C3; 0))

Result: €260.00€ 260,00

Watch out: Leave out the + 1 and the result is 600 × 12 / 30 = 240, silently 20 short, and an asset put into use on the 1st is depreciated for one day less than a full month.

=D3 * (EOMONTH(C3, 0) - C3 + 1) / (EOMONTH(C3, 0) - EOMONTH(C3, -1))

=D3 * (EOMONTH(C3; 0) - C3 + 1) / (EOMONTH(C3; 0) - EOMONTH(C3; -1))

=EOMONTH(C3, -1) + 1

=EOMONTH(C3; -1) + 1

Your client's fiscal year starts on May 1, so its quarters end in July, October, January and April. For interim testing you want the fiscal quarter end next to every journal entry you selected, and the same formula must still work if the fiscal year start moves. The start month sits in F1.

Formula in D4

=LET(
  entryDate, B4,
  fyStart, $F$1,
  monthsToEnd, MOD(fyStart - 1 - MONTH(entryDate), 3),
  EOMONTH(entryDate, monthsToEnd)
)

=LET(
  entryDate; B4;
  fyStart; $F$1;
  monthsToEnd; MOD(fyStart - 1 - MONTH(entryDate); 3);
  EOMONTH(entryDate; monthsToEnd)
)

Result: 31 Jan 2026

Watch out: The mirror-image mistake: EOMONTH(entryDate, 3) reads as 'the end of the quarter' but is the end of the month three months later, 28 Feb 2026 here. It is wrong for every entry: the correct offset is always 0, 1 or 2, never 3.

=EOMONTH(D4, -3) + 1

=EOMONTH(D4; -3) + 1