Excel won't accept 7:12 as a time

[Guest]

Cell uses custom format h:mm
Validation set to be at least 3 characters
Validation error set to STOP if fails validation.

I can enter 7:10, 7:11, 7:13, 7:14, etc

I can enter 5:12, 6:12, 8:12, etc

Just not 7:12, anybody got an answer?

 

[Guest]

I can send the spreadsheet if anyone thinks they can help.

 

[Jerry W. Lewis]

It is probably some setting on your end, but you have given too little
information for a diagnosis. For instance what happens when you try to
enter 7:12? What are the exact validation formulas? What happens when
you enter 7:12 in a cell that has no validation? What version of Excel
and what operating system?

Jerry

 

[Guest]

XP w/SP2
Excel 2002 w/SP3

Validation by:

Allow:
Text Length

Data:
Greater Than

Minimum:
3

All unlocked cells have the same validation, all exhibit the same problem.

 

[Jerry W. Lewis]

But what does happen when you try to enter 7:12? If the value changes
to something else, you might check under Tools|AutoCorrect Options to
see if someone defined a substitution that would impact 7:12.

In Excel 2002 w/SP2 under Win Me, I experience no problem entering 7:12
with the specified cell validation.

As a temporary workaround, until we figure out what is different about
your configuration, you could enter 0.3 and format as time, since times
are stored as formatted fractions of a day.

Jerry

 

[Guest]

When I enter 7:12 it brings up the STOP message from the validation rule.
Obviously this means Excel thinks I have not entered at least 4 characters,
but obviously I have.

Still confused.

 

[Jerry W. Lewis]

What exactly are your validation settings? My interpretation of what
you said is
Text Length
greater than or equal to
3
which does allow 7:12 in my installation of Excel 2002.

I do get a validation failure like you describe if I use
Text Length
greater than
3
but that requires at least 4 characters, and so does not match your
description.

The reason that 7:12 fails a validation of at least 4 text characters is
that Excel coerces the number (0.3, not the formatted number) to text
and finds only 3 characters ... Similarly, 2:24 (0.1), 4:48 (0.2), 9:36
(0.4), 12:00 (0.5), 14:24 (0.6), 16:48 (0.7), 19:12 (0.8), and 21:36
(0.9) would all fail.

Perhaps a better way to validate input in this cell would be
Time
greater than or equal to
1:00

Jerry