Others

How to Set Conditional Time Entry Data Validation in Excel

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to restrict a specific cell to only accept a valid 24-hour time format based on a text condition in another cell.

Product: Excel
Device & OS: not provided
Scenario: Setting up conditional data entry rules for time logs, schedules, or tracking sheets where inputs depend on prerequisite fields.
Observed behavior: The target cell requires a validation rule to ensure it accepts a time value only when the prerequisite cell meets a specific condition (e.g., does not contain '--').

Before you start

Ensure you know exactly which cell will receive the time entry and which cell acts as the reference condition before applying the validation formula.

Solution 1Recommended

Use a Custom Data Validation Formula

Apply a custom data validation rule using the AND, ISNUMBER, and inequality functions to enforce the conditional 24-hour time entry.

By combining multiple logical functions into a single Custom Data Validation rule, you can force Excel to check both the format of the inputted time and the contents of a dependent cell before accepting the data.

Format the target cell

Select the target cell (e.g., D12), right-click, and choose 'Format Cells'. Under the Number tab, select 'Time' and choose the 24-hour format (hh:mm), then click OK.

Open Data Validation

Keep the target cell selected, navigate to the 'Data' tab on the Excel ribbon, and click on 'Data Validation' in the Data Tools group.

Enter the custom formula

In the Data Validation dialog box, go to the 'Settings' tab. Change the 'Allow' dropdown to 'Custom', and enter the following formula: =AND(ISNUMBER(D12),D12>=0,D12<1,D9<>"--")

Set up an error alert

Switch to the 'Error Alert' tab, ensure 'Show error alert after invalid data is entered' is checked, and type a custom message explaining the 24-hour format and the dependency requirement. Click 'OK' to apply.

Understanding the validation formula: ISNUMBER ensures the input is numeric. D12>=0 and D12<1 restrict the value to a valid 24-hour time (since Excel stores time as a decimal fraction of a day). D9<>"--" enforces the text condition from the reference cell.

WPS Spreadsheet Solution

Easily Set Conditional Data Validation with WPS Spreadsheet

WPS Spreadsheet fully supports advanced data validation and custom formulas, allowing you to create complex data entry rules exactly as you would in Microsoft Excel.

1. Open your workbook: Launch WPS Spreadsheet and open the file where you need to apply the conditional time entry.
2. Format the cell: Right-click the target cell, select 'Format Cells', and apply your preferred 24-hour Time format.
3. Apply Data Validation: Navigate to the Data tab on the ribbon, click 'Validation', choose 'Custom', and paste your conditional AND formula.
4. Save and Test: Set your desired input messages or error alerts, click OK, and test the cell to ensure it behaves correctly based on the reference cell.

Fully compatible with Microsoft Excel formulas and data validation rules.Clean, intuitive interface for managing complex conditional data entry.Free and lightweight office suite for seamless data management and productivity.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel store time values between 0 and 1?

Excel represents dates as whole numbers and times as fractional parts of a day. Therefore, any time within a standard 24-hour period is evaluated internally as a decimal greater than or equal to 0 and less than 1.

Can I apply this validation to an entire column instead of a single cell?

Yes. Select the entire range (e.g., D2:D100) before opening the Data Validation dialog. Ensure your formula uses relative references (like D2 and D9) instead of absolute references ($D$2) so the rule adjusts automatically for each row.

How do I change the error message when someone types an invalid time?

Inside the Data Validation dialog box, click on the 'Error Alert' tab. Ensure 'Show error alert after invalid data is entered' is checked, choose your alert style (Stop, Warning, or Information), then type your custom Title and Error message.

Office Quick Solutions