B
Bert
I have a couple of questions about validating times.
Question 1:
In any cell in Column B, I want to allow valid times in the HH:MM AM/PM
format, plus I want to allow "Noon" and "Midnight". Column B is formatted
for time in the same format (HH:MM AM/PM). I found a validation formula
that does a similar thing for dates, and after playing around for a while, I
came up with this--which I put in the Custom field in the Validation Dialog.
=OR(F2="Noon",F2="Midnight",AND(N(F2)>=0,N(F2)<0.99999))
It doesn't work, but IF I change the ">=" to just ">" it works--more or
less; that is, bogus times like 10:70 AM and 13:00 PM are rejected (but not
13:00 w/o the PM, which is fine.).
BUT...I can't enter 12:00 AM (Midnight). And a minor thing: it allows
"noon" and "midnight", but is there a way to force an initial capitalized
letter on each word, e.g., "Noon" and "Midnight"? Now if I enter mIdNighT,
it accepts it. (I tried PROPER and EXACT but w/o success. Maybe there's a
simpler/better way to do this without validation, but I'm not sure how.
Question 2: How do I modify or apply this (or the proper formula, that is),
so that it works for every cell in column B?
Thanks.
Bert
Question 1:
In any cell in Column B, I want to allow valid times in the HH:MM AM/PM
format, plus I want to allow "Noon" and "Midnight". Column B is formatted
for time in the same format (HH:MM AM/PM). I found a validation formula
that does a similar thing for dates, and after playing around for a while, I
came up with this--which I put in the Custom field in the Validation Dialog.
=OR(F2="Noon",F2="Midnight",AND(N(F2)>=0,N(F2)<0.99999))
It doesn't work, but IF I change the ">=" to just ">" it works--more or
less; that is, bogus times like 10:70 AM and 13:00 PM are rejected (but not
13:00 w/o the PM, which is fine.).
BUT...I can't enter 12:00 AM (Midnight). And a minor thing: it allows
"noon" and "midnight", but is there a way to force an initial capitalized
letter on each word, e.g., "Noon" and "Midnight"? Now if I enter mIdNighT,
it accepts it. (I tried PROPER and EXACT but w/o success. Maybe there's a
simpler/better way to do this without validation, but I'm not sure how.
Question 2: How do I modify or apply this (or the proper formula, that is),
so that it works for every cell in column B?
Thanks.
Bert