Flag Dates That Fall Outside The Reporting Period

Excel Formulas › Auditing & Error-Proofing

All versions

A month-end report is only right if every line belongs to the month. Dates drift: an invoice keyed with last month's date, a return posted to the wrong period, a typo that puts a September sale in 2025. One IF with an OR inside compares each date against the period's start and end and flags anything outside. Filter on the flag and you have the review list before the report goes out.

Quick formula: Flag if the date is before the start or after the end of the period:

=IF(OR(B2<C2,B2>D2),"Outside period","OK")

For a September 1–30 period, 9/14 is OK; 8/29 and 10/1 are both flagged.

Functions used (tap for the full reference guide):

IF OR COUNTIFS

The example

Three invoices against a September reporting window. Two are out of period — one early, one late.

	A	B	C	D	E
1	Invoice	Date	Period start	Period end	Status
2	INV-1041	9/14/2026	9/1/2026	9/30/2026	OK
3	INV-1042	8/29/2026	9/1/2026	9/30/2026	Outside period
4	INV-1043	10/1/2026	9/1/2026	9/30/2026	Outside period

The formula

Filled down the invoice list:

=IF(OR(B2<C2,B2>D2),"Outside period","OK") // before start OR after end gets the flag

How it works

Two comparisons joined by OR:

B2<C2 is TRUE when the date is before the period start. 8/29 is before 9/1.
B2>D2 is TRUE when the date is after the period end. 10/1 is after 9/30.
OR(...) is TRUE if either test is. A date can only fail one of them, but the OR catches both directions in one formula.
IF(..., "Outside period", "OK") turns the TRUE/FALSE into a readable flag you can filter or count.

In practice the start and end live in two header cells and the formula uses absolute references: =IF(OR(B2<$H$1,B2>$H$2),...). The example shows them per row so you can see the comparison.

Try it: interactive demo

Interactive

Enter a transaction date and the period start and end.

Transaction date (YYYY-MM-DD)

Period start

Period end

Variations

Count how many are out of period

One summary cell for the top of the report.

=COUNTIF(E2:E500,"Outside period") or =COUNTIFS(B2:B500,"<"&$H$1)+COUNTIFS(B2:B500,">"&$H$2)

Say which way it is out

IFS gives a more useful flag for the reviewer.

=IFS(B2<$H$1,"Before period",B2>$H$2,"After period",TRUE,"OK")

Pitfalls & errors

Dates stored as text will not compare correctly — a text "9/14/2026" is greater than any real date. Check with ISNUMBER(B2) first, or convert with DATEVALUE.

Use the same flag as a conditional-formatting rule: =OR(B2<$H$1,B2>$H$2) as the formula, red fill, and the out-of-period rows light up without a helper column.

If the dates carry a time (9/30/2026 14:05), B2>D2 flags an afternoon on the last day as outside the period. Compare INT(B2) or set the end to 9/30/2026 23:59.

Practice workbook

📊

Download the free Flag Dates That Fall Outside The Reporting Period practice workbook

Edit the yellow date and period cells; the status recalculates.

Frequently asked questions

Why not use AND for the in-range test?

You can: =IF(AND(B2>=C2,B2<=D2),"OK","Outside period") is the same logic inverted. OR reads more naturally as "flag if either boundary is crossed", and it extends to extra conditions like a missing date more cleanly.

How do I set the period to the current month automatically?

Start: =DATE(YEAR(TODAY()),MONTH(TODAY()),1). End: =EOMONTH(TODAY(),0). Put them in the two header cells and the flag rolls forward every month with no edits.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: IF OR COUNTIFS