what's happening?

A

alekm

Hi
I formmated a cell as Number with 0 decimal places and I set Whole Number in Validation section
I expected only whole numbers would be allowed, and it's ok when I try to enter 1,1 excel won'
let me do that, but when I type for example: 1.1 and
press Enter I get 37987! When I type 3.55 and press Enter I get 20149! And so on..
What's happening
Than

alekm
 
F

Frank Kabel

Hi
seems you're using the coma as decimal separator and the
dot as delimiter for dates. So what's happening is that
Excel interprets your entry '1.1' as a data (1-Jan-2004)
As dates are stored as (integer) numbers this date is
represented by 37987 which is a valid entry.
AFAIK no chance to prevent this as dates are internally
stored as numbers and Excel does not know if you want to
enter a number like 37987 or if you want to enter a ate
-----Original Message-----
Hi,
I formmated a cell as Number with 0 decimal places and I
Click to expand...
set Whole Number in Validation section.
I expected only whole numbers would be allowed, and it's
Click to expand...
ok when I try to enter 1,1 excel won't
 
J

JE McGimpsey

XL is interpreting your entry as a date. (e.g., 1.1 = 1 January 2004).
Since XL stores dates as whole number offsets (e.g., 37987 days) from a
base date (31 December 1899), they meet the criteria.
 
A

alekm

Is there any way I can ensure all non whole number values are rejected without VBA
alekm
 
Top