INT

You are working through the client's receivables aging list (ouderdomsanalyse debiteuren). For each open invoice you have the number of days past the due date, and you want to know how many full 30-day periods it is overdue, to sort invoices into the 30, 60 and 90-day bands.

An invoice 95 days overdue has passed 3 full periods; the list should say 3.

Formula in C2

=INT(B2 / 30)

=INT(B2 / 30)

Result: 33

Watch out: Invoices not yet due have negative days overdue. INT(-10 / 30) gives -1, not 0, so a flagged invoice would show up as a minus one period. Test the sign first, for example with MAX(0, B2).

=B2 - INT(B2 / 30) * 30

=B2 - INT(B2 / 30) * 30

You are reconciling the receivables ledger (grootboekrekening 1300) to the client's aging list, customer by customer. The differences are positive and negative.

To decide which ones to follow up, you want each difference labelled with the start of its band of 500 euro (from -1,5001.500 up to -1,0001.000, from 0 up to 500, and so on). The band width sits in E2 so you can change it.

Formula in C2

=INT(B2 / $E$2) * $E$2

=INT(B2 / $E$2) * $E$2

Result: -€1,500.00€ -1.500,00

Watch out: Without the $ signs, C3 divides by E3, which is empty, and returns #DIV/0!. The band width must be locked.

=FLOOR.MATH(B2, $E$2)

=FLOOR.MATH(B2; $E$2)

In journal entry testing you have the manual entries (dagboek MEM) with the date and time the system created them. You want the number of entries created on 31 December 2025, the date in E1, for your cut-off work.

COUNTIF on the timestamp column does not work, because a timestamp at 09:10 is not equal to the date itself, so the formula has to compare the date part of each timestamp.

Formula in E2

=SUMPRODUCT(--(INT(B2:B7) = E1))

=SUMPRODUCT(--(INT(B2:B7) = E1))

Result: 44

Watch out: COUNTIF(B2:B7, E1) returns 1 here, not 4: only 25-0902 at 00:00 is exactly equal to the date. It looks like a quiet year-end, which is why the error goes unnoticed.

=COUNTIFS(B2:B7, ">=" & E1, B2:B7, "<" & E1 + 1)

=COUNTIFS(B2:B7; ">=" & E1; B2:B7; "<" & E1 + 1)

=B3 - INT(B3)

=B3 - INT(B3)

In journal entry testing you select manual entries (dagboek MEM) that were created at an unusual moment:

• on a Saturday or Sunday
• before 07:00
• from 19:00 onwards

The auditfile gives the creation moment as one date-time. You want a 1 for every entry that meets one of these criteria and a 0 for the others. An entry created at exactly 07:00 on a weekday is within office hours.

Formula in C2

=LET(
  created, B2,
  day, INT(created),
  hours, ROUND((created - day) * 24, 2),
  IF(OR(WEEKDAY(day, 2) > 5, hours < 7, hours >= 19), 1, 0)
)

=LET(
  created; B2;
  day; INT(created);
  hours; ROUND((created - day) * 24; 2);
  IF(OR(WEEKDAY(day; 2) > 5; hours < 7; hours >= 19); 1; 0)
)

Result: 11

Watch out: The limits 7 and 19 are typed into the formula. When the client has other office hours, you have to find them inside the formula. Put them in cells and refer to them with absolute references.

=IF(OR(WEEKDAY(INT(B2), 2) > 5, ROUND((B2 - INT(B2)) * 24, 2) < 7, ROUND((B2 - INT(B2)) * 24, 2) >= 19), 1, 0)

=IF(OR(WEEKDAY(INT(B2); 2) > 5; ROUND((B2 - INT(B2)) * 24; 2) < 7; ROUND((B2 - INT(B2)) * 24; 2) >= 19); 1; 0)