Forum Discussion

Scott Morrow's avatar
Scott Morrow
Copper Contributor
Apr 12, 2017

Data validation formula to return 24hr time or N/a

Hi, I'm wondering if some kind person might be able to provide some assistance.  I need a field in Excel to either accept the value "N/a" or time (in 24hour format) when entered directly into the cell.  I've used data validation and started the following formula

=OR(C13="N/A",AND(ISNUMBER(C13),C13=TIME(            ))).

 

I'm stuck on the last part of the formula. What should I be entering in the brackets after time so that a user can enter the 24hr time. Hoping this makes sense and is even possible.  Thanks in advance

Formulas & Functions
Need Help

2 Replies

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Apr 12, 2017
    Hi Scott,

    Just format C13 with 24 hours time format and in data validation formula check if number entered is between 0 and 1. That's equivalent of time between 00:00 and 24:00, more exactly another representation of it.

Resources