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?