DAYS

You are testing cut-off in the XAF auditfile of Veldhuis Installatie BV. For each entry you want to know how many calendar days passed between the invoice date and the date the entry was posted, so you can pick out purchase invoices from December 2025 that were only booked in the new year.

Formula in D2

=DAYS(C2, B2)

=DAYS(C2; B2)

Result: 44

Watch out: Written as DAYS(B2, C2), in the order the columns appear, D2 shows -4. The size is right and only the sign gives the mistake away.

You sent debtor confirmations at year end and are following up on the replies. For each customer you want the days between the reply deadline and the date the reply arrived: positive when the reply was late, negative when it was early.

Customers who have not replied yet should say so instead of showing a meaningless figure. The formula is copied down column D.

Formula in D2

=IF(C2 = "", "Not received", DAYS(C2, B2))

=IF(C2 = ""; "Not received"; DAYS(C2; B2))

Result: 44

Watch out: Averaging column D with AVERAGE skips the text Not received, which is right, but it also lets early replies cancel out late ones. Report late and early separately if that matters for your follow-up.

=IF(C2 = "", "Not received", C2 - B2)

=IF(C2 = ""; "Not received"; C2 - B2)

You reconcile the receivables aging list to the ledger at 31 December 2025. Every open invoice goes into an age band, counted in days since the invoice date and measured at the report date in G1:

• 0-30
• 31-60
• 61-90
• 91+

The band table sits in I1:J5 so the limits can be changed. An invoice dated after the report date is a cut-off error and must stand out.

Formula in D2

=XLOOKUP(DAYS($G$1, B2), $I$2:$I$5, $J$2:$J$5, "Check date", -1)

=XLOOKUP(DAYS($G$1; B2); $I$2:$I$5; $J$2:$J$5; "Check date"; -1)

Result: 61-90

Watch out: If the bucket starts are typed as 0, 30, 60 and 90, an invoice exactly 30 days old lands in 31-60 while its label promises 0-30. Nothing errors; the aging list is quietly off at every boundary and will not agree with the client's own specification.

=XLOOKUP($G$1 - B2, $I$2:$I$5, $J$2:$J$5, "Check date", -1)

=XLOOKUP($G$1 - B2; $I$2:$I$5; $J$2:$J$5; "Check date"; -1)

You assess how quickly Veldhuis Installatie BV's customer D1001 pays, as input for your view on the allowance for doubtful debts. A plain average treats a small invoice like a large one, so you want the days to pay weighted by amount, over settled invoices only; one invoice is still open.

The formula must handle the blank payment date explicitly and stay readable in the working papers.

Formula in G1

=LET(
  invoiced, B2:B6,
  paid, C2:C6,
  amount, D2:D6,
  settled, ISNUMBER(paid),
  days, IF(settled, DAYS(paid, invoiced), 0),
  weight, amount * settled,
  SUM(days * weight) / SUM(weight)
)

=LET(
  invoiced; B2:B6;
  paid; C2:C6;
  amount; D2:D6;
  settled; ISNUMBER(paid);
  days; IF(settled; DAYS(paid; invoiced); 0);
  weight; amount * settled;
  SUM(days * weight) / SUM(weight)
)

Result: 39.0039,00

Watch out: The mirror-image mistake is DAYS(invoiced, paid), start first as in NETWORKDAYS. The result is -39; wrapping it in ABS makes it look right but also hides any payment booked before its invoice date, a data error you want to see.

=AVERAGE(DAYS(FILTER(C2:C6, ISNUMBER(C2:C6)), FILTER(B2:B6, ISNUMBER(C2:C6))))

=AVERAGE(DAYS(FILTER(C2:C6; ISNUMBER(C2:C6)); FILTER(B2:B6; ISNUMBER(C2:C6))))