I need to restrict data entry to non-times. All other data is ok. But
once a time is entered with a colon my scheduling template formats all
future data entered in that cell as a time. How do I do data
validation to exclude times?

format the cell as Text or preceed your entries with a single apostrophe as
'14:22 to force the entries as text.

" wrote:

I need to restrict data entry to non-times. All other data is ok. But
once a time is entered with a colon my scheduling template formats all
future data entered in that cell as a time. How do I do data
validation to exclude times?

On Jun 15, 10:10 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:
format the cell as Text or preceed your entries with a single apostrophe as
'14:22 to force the entries as text.

" wrote:
I need to restrict data entry to non-times. All other data is ok. But
once a time is entered with a colon my scheduling template formats all
future data entered in that cell as a time. How do I do data
validation to exclude times?

I actually need to have entries to 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? Thanks

Side note #1: The reason 10:00 showed up as 0.41667 is that 10 a.m. is .41667
of the way through a 24 hour period. That exercies gave you a little insight
as to how Excel thinks of time entries internally. Multiply 24 by .41667 and
see what you get.

Excel just insists that a number with a colon in it is a time value. I
couldn't even set up a custom number format as 00:00 without Excel telling me
I couldn't do that!

Side note #2: You said "...once a time is entered with a colon, my
scheduling template format all future data entered in that cell as a time."
So are you using one cell for entering different types of data?

But back to the issue at hand. While you cannot use data validation to
restrict ONE specific type of entry into a cell, you can use it to only allow
one particular type of data to be entered into a cell. Choose the cell then
choose Data | Validation and you will see that you have several choices for
"Allow":
Any Value
Whole Number
Decimal
List
Date
Time
Text length
Custom

You could choose one of the number types (Whole Number or Decimal) and that
would restrict entries to that type of numeric entries.

I think that if you need to make entries like 10:00 sometimes and 3.14159 at
other times, then you're going to need a VBA solution. The
Worksheet_Change() event could probably be used to help you out in this
situation, but more information about what type of data might be entered into
that cell would be needed. Or maybe I just don't fully understand the
problem - I'm currently under the impression that we are just talking about
one single cell on the sheet, and maybe you're talking about several
different cells?

" wrote:

On Jun 15, 10:10 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:
format the cell as Text or preceed your entries with a single apostrophe as
'14:22 to force the entries as text.

" wrote:
I need to restrict data entry to non-times. All other data is ok. But
once a time is entered with a colon my scheduling template formats all
future data entered in that cell as a time. How do I do data
validation to exclude times?

I actually need to have entries to 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? Thanks