WEEKDAY

You are planning your attendance at the year-end stock counts of Veldhuis Installatie BV. The client's count schedule lists the count date per location. You want the day of the week as a number, with Monday as 1, so you can see at a glance which counts fall on a weekend.

Formula in C3

=WEEKDAY(B3, 2)

=WEEKDAY(B3; 2)

Result: 66

Watch out: Dates in an exported specification often arrive as text, for example 27.12.2025 or n/a left-aligned in the cell. When Excel cannot read the text as a date, WEEKDAY returns #VALUE!, depending on the format and your regional settings. Real dates align right by default; text aligns left.

For journal entry testing you want every line of the XAF export marked as a weekend posting or a weekday posting, because weekend entries deserve a closer look. The formula goes in column D and is copied down. One line has no posting date.

Formula in D3

=IF(WEEKDAY(A3, 2) > 5, "Weekend", "Weekday")

=IF(WEEKDAY(A3; 2) > 5; "Weekend"; "Weekday")

Result: Weekend

Watch out: The blank date in row 6 is marked Weekend, so a line you cannot date lands in your weekend selection for the wrong reason. Test for an empty date first, and ask the client why the posting date is missing.

=CHOOSE(WEEKDAY(A3, 2), "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun")

=CHOOSE(WEEKDAY(A3; 2); "Mon"; "Tue"; "Wed"; "Thu"; "Fri"; "Sat"; "Sun")

=TEXT(A3, "dddd")

=TEXT(A3; "dddd")

In the sales journal (VRK) of Veldhuis Installatie BV you want to know how much was invoiced on a Saturday or Sunday in the weeks before year end, because weekend sales near the cut-off are a classic cut-off risk. The export has the posting date, the entry number and the credit amount, and you want one cell with the total, without a helper column.

Formula in E2

=SUMPRODUCT((WEEKDAY(A2:A8, 2) > 5) * C2:C8)

=SUMPRODUCT((WEEKDAY(A2:A8; 2) > 5) * C2:C8)

Result: €20,100.00€ 20.100,00

Watch out: Leave out the 2 and the test > 5 picks Fridays and Saturdays: 18,45018.450 + 6,3006.300 + 9,6009.600 = 34,35034.350. That total looks plausible and is wrong.

=SUM(FILTER(C2:C8, WEEKDAY(A2:A8, 2) > 5))

=SUM(FILTER(C2:C8; WEEKDAY(A2:A8; 2) > 5))

=SUMPRODUCT(--(WEEKDAY(A2:A8, 2) > 5))

=SUMPRODUCT(--(WEEKDAY(A2:A8; 2) > 5))

For the search for unrecorded liabilities you need to know when each approved purchase invoice was paid. The client has a payment run only on Tuesdays and Fridays, and an invoice never goes in the run of the day it is approved. For every invoice, the sheet must show the next payment run date, so you can see which payments fall after year end 31 December 2025.

Invoice INK-4472 was approved on Wednesday 31 Dec 2025, so it goes in the Friday run.

Formula in D3

=LET(
  approved, B3,
  dayNum, WEEKDAY(approved, 2),
  daysToAdd, CHOOSE(dayNum, 1, 3, 2, 1, 4, 3, 2),
  approved + daysToAdd
)

=LET(
  approved; B3;
  dayNum; WEEKDAY(approved; 2);
  daysToAdd; CHOOSE(dayNum; 1; 3; 2; 1; 4; 3; 2);
  approved + daysToAdd
)

Result: 2 Jan 2026

Watch out: The mirror-image mistake: WEEKDAY(approved) without the 2 makes Wednesday 4, CHOOSE picks 1, and the result is Thursday 1 Jan 2026. It is a real date one day off, and nothing flags it.

=B3 + CHOOSE(WEEKDAY(B3, 12), 3, 2, 1, 4, 3, 2, 1)

=B3 + CHOOSE(WEEKDAY(B3; 12); 3; 2; 1; 4; 3; 2; 1)

=WORKDAY.INTL(B3, 1, "1011011")

=WORKDAY.INTL(B3; 1; "1011011")