DAY

In the XAF auditfile of Veldhuis Installatie BV every entry has a posting date. For your journal entry testing you want the day of the month on which each entry was posted, so you can see how many were booked in the last days of a period.

Formula in C2

=DAY(B2)

=DAY(B2)

Result: 2929

Watch out: A day number alone cannot tell 2 January from 2 December. When the month matters, as in a cut-off test, combine DAY with MONTH or YEAR, or test the whole date.

For the cut-off test on the bank statement you want the day of the month of each value date. One line in the extract has no value date yet. Without a guard, DAY would turn that empty cell into a 0 and the line would look like a valid but strange date, so the sheet should say that the date is missing.

Formula in D2

=IF(B2 = "", "No value date", DAY(B2))

=IF(B2 = ""; "No value date"; DAY(B2))

Result: 3030

Watch out: A cell with a space instead of a date is not equal to empty text, so it falls through to DAY and gives #VALUE!. That error is at least visible; check such cells in the extract.

=IF(ISBLANK(B2), "No value date", DAY(B2))

=IF(ISBLANK(B2); "No value date"; DAY(B2))

In your journal entry testing, manual entries posted on the last day of a month that falls on a Saturday or Sunday deserve a closer look: nobody is at the office to approve them. You need one formula that recognizes a month-end date, whatever the month length, and checks the day of the week.

Formula in C2

=IF(AND(DAY(B2 + 1) = 1, WEEKDAY(B2, 2) > 5), "Weekend month end", "OK")

=IF(AND(DAY(B2 + 1) = 1; WEEKDAY(B2; 2) > 5); "Weekend month end"; "OK")

Result: Weekend month end

Watch out: Testing DAY(B2) = 31 for the month end silently misses 30 September and 30 November: the formula would flag only the two entries on the 31st and call the 30 November entry OK, with no error.

=IF(AND(B2 = EOMONTH(B2, 0), WEEKDAY(B2, 2) > 5), "Weekend month end", "OK")

=IF(AND(B2 = EOMONTH(B2; 0); WEEKDAY(B2; 2) > 5); "Weekend month end"; "OK")

A bank interface delivers posting dates partly as real dates and partly as text in the layout dd-mm-yyyy, depending on the file. You need the day of the month for every row, and the result must not change when a colleague opens the sheet with a different regional setting. DAY on the text cells would give #VALUE! or a different day depending on the setting.

Formula in D2

=LET(
  raw, B2,
  posted, IF(ISNUMBER(raw), raw, DATE(RIGHT(raw, 4), MID(raw, 4, 2), LEFT(raw, 2))),
  DAY(posted)
)

=LET(
  raw; B2;
  posted; IF(ISNUMBER(raw); raw; DATE(RIGHT(raw; 4); MID(raw; 4; 2); LEFT(raw; 2)));
  DAY(posted)
)

Result: 3030

Watch out: The mirror-image mistake: the cut assumes dd-mm-yyyy. A text date in the layout mm-dd-yyyy is read with day and month swapped, so 02-01-2026 meant as 1 February gives day 2 instead of 1, with no error. Confirm the layout with the source before you rely on the column.

=DAY(B2)

=DAY(B2)