Function Problems

Fix Excel Data Validation for Date and Time Values at Midnight

Natalie TaylorNatalie Taylor Nov 12, 2026 869 views

Question details

The user needs a reliable way to validate date-time entries, especially values entered at midnight.

Product
WPS Spreadsheet
Device & OS
not provided
Scenario
A MOD-based custom data-validation formula is being used to test date-time values in a spreadsheet.
Observed behavior
The formula =MOD(A3,2) does not provide a meaningful date-time validation test and may behave unexpectedly when the time is midnight.
Before you start

Decide whether users need to enter a date and a time separately or as one combined value. Check the allowed date range and time range before opening the Data Validation settings.

Solution 1Recommended

Validate the date and time in separate cells

Use this approach when date-time input must be reliable, readable, and easy to restrict at midnight.

Spreadsheet dates and times are stored as serial numbers: the whole-number portion represents the date, while the fractional portion represents the time. At midnight, the time portion is zero, so =MOD(A3,2) tests the parity of the serial number rather than whether the time is valid.

Use one cell for the date and another for the time. You can display the two values together later with a custom date-time format or a formula without combining the validation rules.

1
Create separate date and time columns

In WPS Spreadsheet, place the date in one cell such as A3 and the time in another cell such as B3. Add headers such as Date and Time so users know where each value belongs.

2
Set date validation

Select the date input range, open the Data tab, choose Data Validation, set Allow to Date, and specify the permitted date range or start and end dates.

3
Set time validation

Select the time input range, open Data > Data Validation, set Allow to Time, and define the permitted start and end times. Include 12:00 AM when midnight is an allowed value.

4
Format the entries

Select the date range and choose a date format from Home > Number Format. Select the time range and choose a time format such as h:mm AM/PM or HH:mm.

5
Show a combined date-time

If a combined display is needed, use a separate display cell with =A3+B3 and apply a custom format such as yyyy-mm-dd h:mm AM/PM. Keep the original date and time cells as the validated inputs.

Validate the date and time in separate cells
Why this works: Midnight is a valid time value represented by zero after the date serial. Independent Date and Time validation rules evaluate the intended values instead of the parity of the complete date-time serial.

Validate date and time entries accurately in WPS Spreadsheet

WPS Spreadsheet provides Data Validation controls for separate date and time inputs, custom formulas for combined date-time values, and flexible formatting so midnight remains a valid and clearly displayed time.

  1. 1. Open the spreadsheet: Open the workbook in WPS Spreadsheet and select the cells intended for date or time entry.
  2. 2. Open Data Validation: Go to the Data tab and choose Data Validation to configure the selected input range.
  3. 3. Choose the validation type: Select Date or Time for separate fields, or Custom for a combined date-time formula.
  4. 4. Apply and test the rule: Confirm the settings, enter a midnight value such as 12:00 AM, and test an invalid value to verify the validation behavior.
Create independent Date and Time validation rules instead of relying on the misleading MOD(A3,2) test.Format separate inputs or a combined display as a readable date-time value.Use custom formulas to enforce date ranges and time windows when one input cell is required.Open and save Microsoft Excel XLSX files with compatible spreadsheet formatting and formulas.
microsoft office alternative - wps office

Frequently Asked Questions

Why does =MOD(A3,2) fail as a date-time validation rule?

MOD(A3,2) returns the remainder after dividing the complete date-time serial number by 2. It tests whether the serial number is even or odd, not whether the date or time is valid. At midnight, the time fraction is zero, which makes the result especially misleading.

How should midnight be represented in a spreadsheet?

Midnight can be entered as 12:00 AM or 00:00, depending on the cell format. Internally, it is the time value zero added to the date serial, so it should be included in the permitted time range when midnight is valid.

Can date and time remain in one cell?

Yes. In WPS Spreadsheet, select the cell range, open Data > Data Validation, choose Custom, and apply a formula that checks the date and time limits. Separate date and time cells are generally easier to validate and edit.

How can I validate dates and times separately in WPS Spreadsheet?

Select the date cells and use Data > Data Validation with Allow set to Date. Then select the time cells and use the same menu with Allow set to Time. Define the permitted limits for each field independently.