onlyoffice · Data validation · Intermediate

Validate Numeric and Date Inputs in ONLYOFFICE

Set numeric, date and custom-formula validation rules, then check boundaries, blanks and pasted values.

Quick answer

Use Data → Data Validation to set separate whole-number, date and Other/custom-formula rules. Test valid endpoints, invalid entries and blanks before entering the final data. Audit pasted values separately; validation is not a security boundary.

Version and evidence

ONLYOFFICE Desktop Editors 10.0.0 documentation baseline, English desktop interface on Windows/Linux; official help and desktop changelog checked 2026-10-19. No native application execution or screenshots were obtained. Exact installed build, OS behavior and saved-file round trips remain manual verification requirements; hosted and mobile behavior is outside scope. The steps below are a documented procedure with independently calculated checkpoints, not a native test report. Use an editable .xlsx practice copy. Do not treat the online help as proof of every older desktop build.

Create an order-entry sheet with three independent checks: an integer quantity, an October delivery date, and an even-number rule. Validation helps guide manual entry, but it is not proof that existing, pasted or imported data is valid. The exercise separates the rule’s mathematical intent from application behavior that you must check.

Set up the input area

Create validation-practice.xlsx, rename the sheet Orders, and enter headers Order, Qty, Delivery, EvenQty in A1:D1. Enter O-01, O-02, O-03, O-04 in A2:A5. Leave B2:D5 blank while configuring rules. Put 10 in F2 and label F1 as Maximum Qty for reference; the numeric rule below uses a literal limit, not an assumed dynamic connection to F2.

Use English formula names and numeric dates. To avoid ambiguous day/month typing later, create dates with DATE, for example =DATE(2026,10,1). If your formula language or separator differs, adjust it consistently before entering the fixture.

Add the three rules

On Orders, select B2:B5. Open Data → Data Validation. In Settings choose Whole number, between, minimum 1, maximum 10. In Input Message enter a short prompt such as Enter 1 to 10 whole units. In Error Alert enable the error message, choose Stop, and use Whole units only with explanatory text. Confirm.
Select C2:C5. Create a Date rule between October 1 and October 31, 2026. Use the dialog’s date controls and confirm the interpreted dates, rather than assuming a numeric string uses your preferred locale. Add a Stop alert explaining the October window.
Select D2 alone. Choose Other and enter the custom Formula =AND(ISNUMBER(D2),D2>=2,D2<=10,MOD(D2,2)=0). Add a Stop message Enter an even whole number from 2 to 10. Confirm. For this controlled exercise, configure D3, D4, and D5 separately, changing each formula’s row reference accordingly. This avoids silently assuming how a multi-cell rule rebases references.

The official dialog uses the name Other for custom-formula validation. Its help documents Settings, Input Message and Error Alert, including Stop, Alert and Message styles. Keep the strict Stop setting for this exercise, but verify the resulting behavior rather than treating the label as a security guarantee.

Run boundary checks before entering the final data

Use B2 for numeric trials, always restoring a valid value before the next test. The intended accepted inputs are 1, 10 and 5; the intended rejected inputs are 0, 11, 2.5 and five. Cancel or correct an invalid entry if an alert appears. Check what is actually stored in the formula bar afterward.

Use C2 to test =DATE(2026,10,1) and =DATE(2026,10,31) as valid endpoints, then September 30 and November 1 as invalid dates. Use D2 to test 2, 10, 4, then 3, 0, and 12. The logical rule is true for the first group and false for the second. Do not proceed if a reference accidentally checks another row.

Blank behavior is a separate result: clear B2, then clear D2 and note whether the application accepts blank cells. The custom formula’s condition is false for a genuinely blank D2, but an application’s blank handling can bypass evaluation. This guide does not assert an Ignore blank control or promise that clearing is blocked. A required-field audit remains necessary.

Enter and audit the final fixture

Restore these values after all tests:

Row	Qty	Delivery formula	EvenQty
2	1	`=DATE(2026,10,1)`	2
3	10	`=DATE(2026,10,31)`	10
4	5	`=DATE(2026,10,15)`	4
5	8	`=DATE(2026,10,20)`	8

Expected totals: quantities 24, even quantities 24. In F4 enter =COUNT(B2:B5); expect 4. In F5 enter =SUM(B2:B5); expect 24. These checks detect missing numeric entries and an aggregate mismatch, but do not replace checking each bound and date. Inspect each of the four custom rules once more.

Save, close and reopen validation-practice.xlsx. Activate Orders; inspect rules on B2, C2 and D2 and confirm the final fixture. Do any renewed invalid-entry probes in a Save As copy named validation-paste-test.xlsx.

Advanced case: pasted data

In that copy, type 99 in H2, copy H2 and paste into B2. Record both the stored result and whether B2 still has its rule. Normal paste may affect validation or bypass an entry alert; no universal behavior is asserted here. Recheck B2 against the explicit 1–10 requirement regardless of the alert. Restore B2 to 1 and reapply its rule if necessary. Repeat with a blank source only if blank handling matters to your workflow.

Troubleshooting

If invalid entries are accepted, inspect Error Alert, style, target range and the stored rule before blaming formulas. If every D cell follows D2, the references were copied incorrectly: configure the affected cell explicitly. If a date appears as a number, apply a Date display format and inspect its underlying value. For list-source controls, see validation dropdowns; this exercise focuses on numeric, date and logical conditions.