data validation

[jbrass]

I have a scheduling template that we use at work. It needs to allow
allow all types of data entry EXCEPT dates and times. Any numbers
entered must be in a format that I can use in formulas.

In other words, I need to have entries that can be numbers or text but
NOT times.
Thus I need the cells formatted as General. General formatting makes
10:00 show up as 0.416667. I need data entries to be restricted to
non-
times. Can I have data validation set up to restrict time entries only
but still be able to enter numbers that can be used in formulas?

I have tried to flag with data validation using Right(A1,3)=":" but
that doesn't seem to work. Any ideas?? Thanks

 

[JLatham]

Per information in the other post on this subject, if an entry has a colon in
it, Excel is going to treat it as a time. But if you'll set the cell format
up as Text then you can enter words or numbers into the cell and if the entry
looks like a number, then it will be treated as a number in formulas
referring to it.

Try this: format a cell (A1?) as text and enter 3.14159 into it. Move over
a couple of columns and enter the formula =A1+15 and the display should be
18.14159 showing that Excel took the text "3.14159" and converted it to a
number before using it in the formula. But that formula is going to give
strange results (#VALUE) when you put a word into A1 and the formula trys to
figure out what you really mean.

Excel also gets similarly confused if the number to be added has a colon in
it. With A1 still formatted as text, enter 10:00 into it, this time you will
see 15.41667 in the cell with the formula because Excel internally converted
the text 10:00 to a time value before adding it to the 15 in the formula.

By the way, your Right(A1,3) should be Mid(A1,3,1) to just pull out the
colon and test it. Right(A1,3) would return ":00" if the entry in A1 is
"10:00". Maybe that will be of some help?

 

[JLatham]

Gary's student has offered a VBA solution in the 3rd posting you've made on
this topic. Please keep it all in one discussion - it helps us all. You get
to see all options, advice and information about it in one place. Others get
to see the same thing and don't cover the same ground again.