Data Validation & Hygiene
Data Validation Restricting Dates to a Specific Year (ISNUMBER, INT & YEAR)
Enforce that entered journal dates are genuine numeric whole dates falling inside an authorized calendar or fiscal year, rejecting text strings, fractional timestamps, and out-of-period dates.
Why YEAR(cell) = 2026 alone lets bad inputs slip through
Writing a simple custom validation rule =YEAR(B2) = $E$2 has two subtle failure modes: first, if a user pastes unparsed text like "2026-06-15" or "Q2 2026", YEAR() can either return #VALUE! or coerce locale-ambiguous strings unexpectedly; second, fractional timestamps like 2026-12-31 18:00 fail exact equality joins against pure calendar dates downstream.
Combining ISNUMBER(B2), INT(B2) = B2, and YEAR(B2) = $E$2 inside AND(...) guarantees three invariants simultaneously: the cell holds a true numeric date serial, contains no hidden time-of-day fraction, and belongs to the open posting year in $E$2.
| Candidate Entry (B2) | ISNUMBER(B2) | INT(B2) = B2 | YEAR(B2) = 2026 | Validation Result |
|---|---|---|---|---|
| 2026-06-15 (Serial) | TRUE | TRUE | TRUE | TRUE (Accepted) |
| 2025-12-31 (Prior year) | TRUE | TRUE | FALSE | FALSE (Closed period) |
| 2026-06-15 14:30 | TRUE | FALSE | TRUE | FALSE (Has time fraction) |
| "June 15, 2026" (Text) | FALSE | — | — | FALSE (Text rejected) |
Strict Calendar-Year Whole-Date Validation Rule
Returns TRUE only when B2 is a true whole-day serial date inside target year $E$2.
=IF(ISNUMBER(B2), AND(INT(B2) = B2, YEAR(B2) = $E$2), FALSE)Fiscal-Year Window Validation (Custom Start Month, e.g. July–June FY)
Validates whole dates falling inside a 12-month fiscal year starting on $F$2 (e.g. 2026-07-01 through 2027-06-30).
=IF(ISNUMBER(B2), AND(INT(B2) = B2, B2 >= $F$2, B2 <= EDATE($F$2, 12) - 1), FALSE)Try the example
Use the original values or change them to test your understanding.