DATEDIF

In the fixed asset register of Veldhuis Installatie BV you want the age of every asset in complete years at the year end of 31 December 2025, to compare with the depreciation period. The register has the purchase date and the year end in two columns.

Formula in E2

=DATEDIF(C2, D2, "Y")

=DATEDIF(C2; D2; "Y")

Result: 44

Watch out: Typing DATEDIF in a cell shows no suggestion and no argument tooltip, because the function is not in the list. Type it in full and check that the formula is accepted.

For the ageing of the receivables you need the age of each open invoice in complete months at the balance date of 31 December 2025.

One invoice in the list is dated 5 January 2026. DATEDIF would return #NUM! for it, but that invoice is exactly the cut-off exception you want to follow up, so the sheet should say so in words.

Formula in D2

=IF(B2 > $G$1, "Dated after balance date", DATEDIF(B2, $G$1, "M"))

=IF(B2 > $G$1; "Dated after balance date"; DATEDIF(B2; $G$1; "M"))

Result: 44

Watch out: Complete months follow the day number. An invoice of 31 October is 2 months old at 31 December, but at 30 November the same invoice is 0 months old, because November has no 31st.

=IFERROR(DATEDIF(B2, $G$1, "M"), "Dated after balance date")

=IFERROR(DATEDIF(B2; $G$1; "M"); "Dated after balance date")

For the lease disclosure you need the remaining term of each lease contract at the balance date of 31 December 2025, written as years and months. The contract list has only the end date.

The balance date is the last day of a month, which makes the month count more delicate than it looks.

Formula in D2

=DATEDIF($G$1, C2, "Y") & " y " & (DATEDIF($G$1, C2, "M") - 12 * DATEDIF($G$1, C2, "Y")) & " m"

=DATEDIF($G$1; C2; "Y") & " y " & (DATEDIF($G$1; C2; "M") - 12 * DATEDIF($G$1; C2; "Y")) & " m"

Result: 2 y 6 m

Watch out: A balance date at a month end combined with a short end month gives a count that is one month lower than the calendar suggests: L-03 ends on 30 November 2026 and shows 10 months, not 11. The result is consistent with the rule, but it surprises a reader who counts months on the calendar. Document the rule in your working paper.

=DATEDIF($G$1, C2, "M")

=DATEDIF($G$1; C2; "M")

=YEARFRAC($G$1, C2)

=YEARFRAC($G$1; C2)

The ageing report for a debtors review at 15 January 2026 must show how old each invoice is in complete months plus the remaining days, for example 4 months and 15 days.

The usual route is DATEDIF twice, once with "M" and once with "MD", but Microsoft warns that "MD" can return negative or wrong numbers. You need a version you can defend in a review.

Formula in D2

=LET(
  since, B2,
  until, $G$1,
  whole, DATEDIF(since, until, "M"),
  rest, until - EDATE(since, whole),
  whole & " m " & rest & " d"
)

=LET(
  since; B2;
  until; $G$1;
  whole; DATEDIF(since; until; "M");
  rest; until - EDATE(since; whole);
  whole & " m " & rest & " d"
)

Result: 4 m 15 d

Watch out: The mirror-image mistake: DATEDIF(since, until, "MD") looks right on most rows and only goes wrong around month ends, so a test on a few rows passes. Check month-end invoices such as F-25-0844 specifically.

=DATEDIF(B2, $G$1, "M")

=DATEDIF(B2; $G$1; "M")

=$G$1 - B2

=$G$1 - B2