Data Validation & Auditing
Custom Data Validation: Numbers Only, Text Only, Whole %, & Weekdays Only
Enforce strict type, whole-percentage integer step, and Monday–Friday business-day rules on intake rows using ISNUMBER, ISTEXT, MOD, and WEEKDAY(date, 2) <= 5.
Four essential custom data-validation gates that stop dirty ERP uploads at the door!
Built-in dropdowns cannot combine type checks, whole-percentage granularity, and business-day scheduling in one composite row gate! Combining four atomic predicates inside AND(...) gives instant row-level QA: (1) ISNUMBER(B2) rejects text-formatted numbers, (2) AND(ISNUMBER(B3), B3 >= 0, B3 <= 1, MOD(ROUND(B3 * 100, 6), 1) = 0) enforces whole-integer percentages like 15% while rejecting fractional 15.5%, (3) ISTEXT(B4) blocks bare numeric codes in cost-center labels, and (4) WEEKDAY(B5, 2) <= 5 (ISO 1=Mon .. 7=Sun) blocks Saturday (6) and Sunday (7) settlement dates!
Notice ROUND(B3 * 100, 6) inside MOD: IEEE-754 binary decimals represent 0.14 * 100 as 14.000000000000002, so rounding to 6 decimal places before MOD(..., 1) prevents false-negative floating-point rejections!
| Field Rule | Validation Predicate | Passes Example | Rejected Example |
|---|---|---|---|
| Numeric Amount Only | ISNUMBER(B2) | 4250 | "$4,250" text string |
| Whole Percentage (0%–100%) | MOD(ROUND(B3 * 100, 6), 1) = 0 | 0.15 (15%) | 0.155 (15.5% fractional) |
| Text Label Only | ISTEXT(B4) | "ACCT-North" | 10400 bare number |
| Monday–Friday Weekday Only | WEEKDAY(B5, 2) <= 5 | 2026-10-30 (Fri = 5) | 2026-10-31 (Sat = 6) |
Composite Row Intake Gate (Number + Whole % + Text + Weekday)
Returns "Valid Record" only when amount B2, whole-percent discount B3, text account code B4, and ISO weekday settlement date B5 all pass simultaneously.
=IF(AND(ISNUMBER(B2), ISNUMBER(B3), B3 >= 0, B3 <= 1, MOD(ROUND(B3 * 100, 6), 1) = 0, ISTEXT(B4), WEEKDAY(B5, 2) <= 5), "Valid Record", "Validation Failed")ISO Day-of-Week Audit Index (1 = Monday .. 7 = Sunday)
Uses return_type = 2 so Monday–Friday map cleanly to 1–5 and weekends map to 6–7.
=WEEKDAY(B5, 2)Try the example
Use the original values or change them to test your understanding.