Combination
Entries posted on weekends and public holidays with FILTER, WEEKDAY and COUNTIF
List the journal entries dated on a Saturday, a Sunday or a Dutch public holiday: WEEKDAY flags the weekends, COUNTIF checks the date against a holiday list, and FILTER keeps the flagged rows.
Functions used:FILTERWEEKDAYCOUNTIFExcel 2021 or later (This combination needs Excel 2021 or later)
Worked example
Journal entries dated on a weekend or a public holiday
Scenario
For the audit of Veldhuis Installatie BV you test journal entries. Entries dated on a Saturday, a Sunday or a public holiday are unusual for a company that works on weekdays, so they carry a higher fraud risk and you want to see them first.
You have the journal lines from the XAF auditfile and a list of the 2025 public holidays in E2:E8. This needs Microsoft 365 or Excel 2021 or later, because of FILTER.
The data
| Row | A | B | C | D | E | F | G | H | I | J |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Entry no. | Date | Description | Amount | Public holidays 2025 | |||||
| 2 | 25-0790 | 14 Mar 2025 | Salaries March | €14,200.00€ 14.200,00 | 1 Jan 2025 | 25-0812=FILTER(A2:D9, (WEEKDAY(B2:B9, 2)>5)+(COUNTIF(E2:E8, B2:B9)>0)>0, "No weekend or holiday entries")=FILTER(A2:D9; (WEEKDAY(B2:B9; 2)>5)+(COUNTIF(E2:E8; B2:B9)>0)>0; "No weekend or holiday entries") | 21 Apr 2025 | Rent invoice | €18,400.00€ 18.400,00 | |
| 3 | 25-0812 | 21 Apr 2025 | Rent invoice | €18,400.00€ 18.400,00 | 21 Apr 2025 | 25-0819 | 26 Apr 2025 | Revenue correction | €96,500.00€ 96.500,00 | |
| 4 | 25-0819 | 26 Apr 2025 | Revenue correction | €96,500.00€ 96.500,00 | 26 Apr 2025 | 25-0838 | 29 May 2025 | VAT correction | €7,250.00€ 7.250,00 | |
| 5 | 25-0838 | 29 May 2025 | VAT correction | €7,250.00€ 7.250,00 | 29 May 2025 | 25-0863 | 12 Oct 2025 | Bonus accrual | €41,200.00€ 41.200,00 | |
| 6 | 25-0851 | 10 Sep 2025 | Maintenance invoice | €52,300.00€ 52.300,00 | 9 Jun 2025 | |||||
| 7 | 25-0863 | 12 Oct 2025 | Bonus accrual | €41,200.00€ 41.200,00 | 25 Dec 2025 | |||||
| 8 | 25-0877 | 21 Nov 2025 | Reclass payables | €3,150.00€ 3.150,00 | 26 Dec 2025 | |||||
| 9 | 25-0880 | 31 Dec 2025 | Year-end revenue | €27,800.00€ 27.800,00 | ||||||
| 10 |
Swipe sideways to see all columns
Formula in F2
=FILTER(A2:D9,(WEEKDAY(B2:B9, 2)>5)+(COUNTIF(E2:E8, B2:B9)>0)>0, "No weekend or holiday entries")
=FILTER(A2:D9;(WEEKDAY(B2:B9; 2)>5)+(COUNTIF(E2:E8; B2:B9)>0)>0; "No weekend or holiday entries")
Result: 25-0812 | 21 Apr 2025 | Rent invoice | €18,400.00 / 25-0819 | 26 Apr 2025 | Revenue correction | €96,500.00 / 25-0838 | 29 May 2025 | VAT correction | €7,250.00 / 25-0863 | 12 Oct 2025 | Bonus accrual | €41,200.0025-0812 | 21 Apr 2025 | Rent invoice | € 18.400,00 / 25-0819 | 26 Apr 2025 | Revenue correction | € 96.500,00 / 25-0838 | 29 May 2025 | VAT correction | € 7.250,00 / 25-0863 | 12 Oct 2025 | Bonus accrual | € 41.200,00
How the pieces fit together
Watch out: A holiday that is missing from the list is never flagged, and the formula gives no warning.
The formula taken apart, from the inside out. Each part can be typed into its own cell to see what it returns.
=WEEKDAY(B2:B9,
2)>5 =WEEKDAY(B2:B9;
2)>5 serial_numberThe date whose day of the week you want.Date column, 8 cellsPress Escape to closereturn_type (optional)Which day is 1: 1 = Sunday (default), 2 = Monday.2: Monday 1 to Sunday 7Press Escape to close>Greater thanPress Escape to closeNumberLiteral valuePress Escape to closeWEEKDAY with return type 2 numbers Monday as 1 up to Sunday as 7, so a value above 5 means Saturday or Sunday. The eight dates give 5, 1, 6, 4, 3, 7, 5, 3, and the test returns FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE, FALSE. Two dates fall in a weekend: 26 April (a Saturday) and 12 October (a Sunday).
=COUNTIF(E2:E8,
B2:B9)>0 =COUNTIF(E2:E8;
B2:B9)>0 rangeThe cells to test.E column, 7 cellsPress Escape to closecriteriaThe condition a cell must meet to be counted.Date column, 8 cellsPress Escape to close>Greater thanPress Escape to closeNumberLiteral valuePress Escape to closeCOUNTIF counts how often each journal date occurs in the holiday list
E2:E8. Because the criteria argument is a range of eight dates, you get eight counts: 0, 1, 1, 1, 0, 0, 0, 0. Above 0 means the date is on the list: 21 April, 26 April and 29 May.=(WEEKDAY(B2:B9,
2)>5)+(COUNTIF(E2:E8, B2:B9)>0)>0 =(WEEKDAY(B2:B9;
2)>5)+(COUNTIF(E2:E8; B2:B9)>0)>0 serial_numberThe date whose day of the week you want.Date column, 8 cellsPress Escape to closereturn_type (optional)Which day is 1: 1 = Sunday (default), 2 = Monday.2: Monday 1 to Sunday 7Press Escape to close>Greater thanPress Escape to closeNumberLiteral valuePress Escape to close+AddsPress Escape to closerangeThe cells to test.E column, 7 cellsPress Escape to closecriteriaThe condition a cell must meet to be counted.Date column, 8 cellsPress Escape to close>Greater thanPress Escape to closeNumberLiteral valuePress Escape to close>Greater thanPress Escape to closeNumberLiteral valuePress Escape to closeAdds the two tests (TRUE counts as 1) and checks that the sum is above 0, which works as OR for each row. The sums are 0, 1, 2, 1, 0, 1, 0, 0, so the result is FALSE, TRUE, TRUE, TRUE, FALSE, TRUE, FALSE, FALSE. Entry
25-0819scores 2 because 26 April 2025 is both a Saturday and King's Day, but it is still one flagged row.=SUMPRODUCT(((WEEKDAY(B2:B9,
2)>5)+(COUNTIF(E2:E8, B2:B9)>0)>0)*1) =SUMPRODUCT(((WEEKDAY(B2:B9;
2)>5)+(COUNTIF(E2:E8; B2:B9)>0)>0)*1) SUMPRODUCT SOMPRODUCTMultiplies ranges pairwise and adds the results.Read more about SUMPRODUCTPress Escape to closeWEEKDAY WEEKDAGThe day of the week as a number.Used here as: array1The first range or array; every other array must have the same size.Read more about WEEKDAYPress Escape to closeserial_numberThe date whose day of the week you want.Date column, 8 cellsPress Escape to closereturn_type (optional)Which day is 1: 1 = Sunday (default), 2 = Monday.2: Monday 1 to Sunday 7Press Escape to close>Greater thanPress Escape to closeNumberLiteral valuePress Escape to close+AddsPress Escape to closerangeThe cells to test.E column, 7 cellsPress Escape to closecriteriaThe condition a cell must meet to be counted.Date column, 8 cellsPress Escape to close>Greater thanPress Escape to closeNumberLiteral valuePress Escape to close>Greater thanPress Escape to closeNumberLiteral valuePress Escape to close*MultipliesPress Escape to closeNumberLiteral valuePress Escape to closeTurns the eight TRUE and FALSE values into 1 and 0 and adds them: 0 + 1 + 1 + 1 + 0 + 1 + 0 + 0 = 4 flagged entries.
=SUMPRODUCT(((WEEKDAY(B2:B9,
2)>5)+(COUNTIF(E2:E8, B2:B9)>0)>0)*D2:D9) =SUMPRODUCT(((WEEKDAY(B2:B9;
2)>5)+(COUNTIF(E2:E8; B2:B9)>0)>0)*D2:D9) SUMPRODUCT SOMPRODUCTMultiplies ranges pairwise and adds the results.Read more about SUMPRODUCTPress Escape to closeWEEKDAY WEEKDAGThe day of the week as a number.Used here as: array1The first range or array; every other array must have the same size.Read more about WEEKDAYPress Escape to closeserial_numberThe date whose day of the week you want.Date column, 8 cellsPress Escape to closereturn_type (optional)Which day is 1: 1 = Sunday (default), 2 = Monday.2: Monday 1 to Sunday 7Press Escape to close>Greater thanPress Escape to closeNumberLiteral valuePress Escape to close+AddsPress Escape to closerangeThe cells to test.E column, 7 cellsPress Escape to closecriteriaThe condition a cell must meet to be counted.Date column, 8 cellsPress Escape to close>Greater thanPress Escape to closeNumberLiteral valuePress Escape to close>Greater thanPress Escape to closeNumberLiteral valuePress Escape to close*MultipliesPress Escape to closeReferenceAmount column, 8 cellsPress Escape to closeMultiplies each 1 or 0 by the amount in column D and adds the products: 18,400 + 96,500 + 7,250 + 41,200 = 163,350. The unflagged amounts are multiplied by 0 and drop out.
=FILTER(A2:D9,
(WEEKDAY(B2:B9, 2)>5)+(COUNTIF(E2:E8, B2:B9)>0)>0, "No weekend or holiday entries") =FILTER(A2:D9;
(WEEKDAY(B2:B9; 2)>5)+(COUNTIF(E2:E8; B2:B9)>0)>0; "No weekend or holiday entries") FILTERReturns every row of a range that meets a condition.Read more about FILTERPress Escape to closearrayThe rows you want back: one column or a whole table.8 rows by 4 columnsPress Escape to closeWEEKDAY WEEKDAGThe day of the week as a number.Used here as: includeOne TRUE/FALSE per row, as tall as array (as wide for columns): what to keep.Read more about WEEKDAYPress Escape to closeserial_numberThe date whose day of the week you want.Date column, 8 cellsPress Escape to closereturn_type (optional)Which day is 1: 1 = Sunday (default), 2 = Monday.2: Monday 1 to Sunday 7Press Escape to close>Greater thanPress Escape to closeNumberLiteral valuePress Escape to close+AddsPress Escape to closerangeThe cells to test.E column, 7 cellsPress Escape to closecriteriaThe condition a cell must meet to be counted.Date column, 8 cellsPress Escape to close>Greater thanPress Escape to closeNumberLiteral valuePress Escape to close>Greater thanPress Escape to closeNumberLiteral valuePress Escape to closeif_empty (optional)What to show when no row passes; without it you get #CALC!.Press Escape to closeFILTER keeps the rows where the combined test is TRUE, with all four columns: 4 rows by 4 columns. The rows are
25-0812,25-0819,25-0838and25-0863. If no entry is flagged, the cell shows the message text instead of #CALC!.
Step by step
- WEEKDAY(B2:B9, 2) gives the weekday numbers 5, 1, 6, 4, 3, 7, 5, 3 (Friday, Monday, Saturday, Thursday, Wednesday, Sunday, Friday, Wednesday). Testing >5 gives FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE, FALSE: 26 April and 12 October are weekend days.
- COUNTIF(E2:E8, B2:B9) looks up each journal date in the holiday list and returns 0, 1, 1, 1, 0, 0, 0, 0. The matches are 21 April (Easter Monday), 26 April (King's Day) and 29 May (Ascension Day). Testing >0 gives FALSE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE.
- Adding the two tests row by row gives 0, 1, 2, 1, 0, 1, 0, 0. Testing the sum >0 gives FALSE, TRUE, TRUE, TRUE, FALSE, TRUE, FALSE, FALSE. The third entry (
25-0819) scores 2, since 26 April 2025 is a Saturday and King's Day, but it counts once. - FILTER keeps the four rows with TRUE and drops
25-0790(Friday 14 March),25-0851(Wednesday 10 September),25-0877(Friday 21 November) and25-0880(Wednesday 31 December). That is 8 - 4 = 4 rows. - The result spills from
F2as 4 rows by 4 columns. Counting the rows gives 4 flagged entries, and adding their amounts gives 18,40018.400 + 96,50096.500 + 7,2507.250 + 41,20041.200 = 163,350163.350.
Why it works
Each test returns one TRUE or FALSE per journal line, so the weekend test and the holiday test can be combined without helper columns. Adding them acts as OR for every row separately (the OR function would give a single answer for the whole range). FILTER then keeps whole rows, so the entry number, description and amount stay next to the flagged date, ready for you to select samples.
Watch out for
The holiday list must be complete and cover the audit year.
The list holds the weekday and Saturday holidays of 2025 (including Whit Monday on 9 June and Boxing Day on 26 December); Easter Sunday and Whit Sunday are left out because the weekend test already flags them. Check the dates against an official calendar and note that Good Friday and 5 May are not holidays for every employer.
- If the file has a date and time in one field, or the dates are text, COUNTIF finds no match for the holidays, since 21 April 2025 at 14:30 is not equal to 21 April 2025. WEEKDAY on text dates can also fail. Convert the column to real dates first, for example with INT for the time part.
- A flagged entry is a risk indicator, not an error. Weekend postings are normal for a client that works in shifts or for automated batch postings, so ask about the usual pattern before you conclude anything. Also note that the posting date can differ from the entry date in the auditfile.
Variations
=FILTER(A2:D9,(WEEKDAY(B2:B9, 2)>5)+ISNUMBER(MATCH(B2:B9, E2:E8, 0))>0, "No weekend or holiday entries")
=FILTER(A2:D9;(WEEKDAY(B2:B9; 2)>5)+ISNUMBER(MATCH(B2:B9; E2:E8; 0))>0; "No weekend or holiday entries")
MATCH with ISNUMBER instead of COUNTIF: MATCH returns the position (2, 3, 4) or #N/A, and ISNUMBER turns that into TRUE or FALSE. Same four rows. It is useful when you also want the position of the holiday in the list.
=IF(OR(WEEKDAY(B2,2)>5, COUNTIF($E$2:$E$8, B2)>0), 1, 0)
=IF(OR(WEEKDAY(B2;2)>5; COUNTIF($E$2:$E$8; B2)>0); 1; 0)
The older approach for Excel versions without FILTER: a helper column in a free column of the sheet (for example column H) with this formula copied down, then filter the sheet on 1 with the normal filter button. The OR function works here because the formula tests one row at a time.
Functions used
- FILTERReturns every row of a range that meets your condition, as a live list that spills into the cells below and updates when the data changes.
- WEEKDAYReturns the day of the week of a date as a number, where return_type decides which day counts as 1.
- COUNTIFCounts how many cells in a range meet one condition, such as equal to a value, above a number or starting with certain letters.
Functions used
- FILTERReturns every row of a range that meets your condition, as a live list that spills into the cells below and updates when the data changes.
- WEEKDAYReturns the day of the week of a date as a number, where return_type decides which day counts as 1.
- COUNTIFCounts how many cells in a range meet one condition, such as equal to a value, above a number or starting with certain letters.