Fix Excel Data Validation for Date and Time Values at Midnight
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.
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.
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.
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.
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.
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.
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.
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.

Use a custom rule for a single date-time cell
Use this method when the date and time must remain in one cell and the allowed range can be described with a formula.
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. Open the spreadsheet: Open the workbook in WPS Spreadsheet and select the cells intended for date or time entry.
- 2. Open Data Validation: Go to the Data tab and choose Data Validation to configure the selected input range.
- 3. Choose the validation type: Select Date or Time for separate fields, or Custom for a combined date-time formula.
- 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.

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.




