A "time" data-validation type, parallel to the date type
Cluster: data-validation
Scenario
A spreadsheet author wants to restrict a cell to a valid time-of-day, the same as Excel's "Allow: Time" data-validation option, with an operator and time-valued bounds: "the entered time must fall between 00:00 and 23:59", or "after 09:00". The library exposes a date validation type but no dedicated time type, so this constraint cannot be expressed and opened by a spreadsheet app as a Time validation. Authors fall back to a custom rule or hand-written XML.
Spec note, not a corpus case: this is a feature proposal for a new validation type, not a bug with a reproduction. The durable value is the format grounding and the API and type shape; a corpus case follows once the authoring surface exists, and can then assert the serialized
type="time"element round-trips.
Desired behavior
- A
timevalidation type sits alongsidedate. A validation such as{ type: 'time', operator: 'between', formulae: ['00:00', '23:59'], allowBlank, showErrorMessage, errorStyle, errorTitle, error }is writable on a cell and produces an OOXML<dataValidation>whosetypeattribute istime, with the operator and formulae serialized so a spreadsheet app presents it as an "Allow: Time" rule. On read, that element parses back into the same typed model. - Time values are day-fraction serials. OOXML and Excel store a time as a fraction of a day, so 12:00 is 0.5, and the persisted
<formula1>and<formula2>carry those fractions. The authoring surface should accept human-readable"hh:mm"strings and numeric fractions, with a defined, documented, timezone-free, locale-independent conversion rule from strings to fractions. - The full comparison operator set applies (
between,notBetween,equal,notEqual,greaterThan,lessThan,greaterThanOrEqual,lessThanOrEqual) mirroring the date type. - The public validation type union adds
'time'so the surface is precisely typed. The types are the docs, and atimevalidation is a first-class member ofST_DataValidationType(date,time,decimal,whole,list,textLength,custom).
Open questions
- Formula representation: accept
"hh:mm"strings, numeric day-fractions, or both? Excel persists the fraction, and a string-to-fraction convenience conversion is desirable but needs a locale-independent, timezone-free rule, plus a decision on seconds ("hh:mm:ss"). - Number format pairing: a time validation usually pairs with a time number format on the cell, so do we auto-apply one, or leave it to the author?
- Share the serialization path with the existing date validation, the closest analog, so operator and two-bound formulae handling is not re-implemented.
Related: multiselect-dropdown-validation, whole-column-data-validation-bounded-memory, list-validation-inline-formula-length-limit, date-validation-formula-never-serializes-nan.