validation of times
 

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.9 9999))
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

Hi!

Question 1:

You say you want to validate column B but your formula
uses references to column F.

Try this formula to validate column B:

=OR(EXACT(B2,"Noon"),EXACT(B2,"Midnight"),AND
(B2=0,B2<=0.99999))

Question 2:

Select the entire range of cells that you want this
validation to apply to then enter the formula in the box.
The cell references will automatically adjust.

Biff

-----Original Message-----
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


.

Thanks, Biff.
Your formula works perfectly.
Yeah, it's Column B, but I was testing it in Column F and forgot to change
it.
Thanks again.
Bert

"Biff" wrote in message
...
Hi!

Question 1:

You say you want to validate column B but your formula
uses references to column F.

Try this formula to validate column B:

=OR(EXACT(B2,"Noon"),EXACT(B2,"Midnight"),AND
(B2=0,B2<=0.99999))

Question 2:

Select the entire range of cells that you want this
validation to apply to then enter the formula in the box.
The cell references will automatically adjust.

Biff

-----Original Message-----
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


.