what's happening?

[Guest]

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

 

[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

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't

 

[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.

 

[Guest]

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

 

[Frank Kabel]

Hi
VBA won't help either as 32716 is a whole number.