cell format: only numbers (no date) with any amount of decimals

Hi,
I need a cell to restrict the input:
-Only numbers are allowed.
-No date posible.
-Any amount of decimals (they must all be shown in the cell).

I tried using the data validation and using the IsNumber() to restrict any non numeral input. The problem with this approach is that if the user enters a date; it apprears as a date format (eg: "5.May"). I'm using an european excel, where the decimal separator is a comma instead of a point; so if a user accidentaly types "5.5" instead of "5,5"; the cell will show "5.May".

I also tried the cell format/number/number format. The problem in here is that I dont know how many decimal positions will the input number have; and I need them all to be shown.

Is there any way to accomplish this?

P.S. Unfortunatelly I am not allowd to use macros to solve this issue.

Thanks a million.

You say no dates are allowed but isn't there an inherent problem insofar as every whole number >=0 is both a valid number and a date
(assumes 1900 date system in place)

Yes I understand - my point is there's no way of preventing user from entering 5.5 rather than 5,5 given both will be interpreted by XL as valid numbers (ie testing for presence of . in the source value won't work).

What are the boundaries of the values to be entered by the user ... ie are you likely to exceed say 39814 (1st Jan 2009) - if not you can use this as the upper boundary - entering 5.5 would go as a date but into current year and would therefore be >= 39814, ie:

=AND(ISNUMBER(A1);A1<39814)

Still wouldn't prevent user from entering 1.1.5 though... but that's seemingly not an issue (ie it's an issue of decimal delimiter)

Hang on... perhaps you could use the format of the cell itself...

Please Login or Register  to view this content.

so the above tests both numeric and cell format remains General - if date is entered it will default to date format (ie <> "G") ... the General format should display the number as entered (ie to as many decimals as entered (or none if whole))

(obviously you need to set format to General before applying the Validation rule!)

Originally Posted by Wizz

Is there a index number for that "General"? so that regardles of the excel language, the CELL() formula allways compares to the General/Standard format?

In all honesty I don't know... what I can say is that there is no "S" format when running UK/US (the S(cientific) is otherwise always followed by decimal indicator, eg S0 so S won't be returned)... if there's no G format on your locale (check XL Help in your version) then you can perhaps adapt to:

Please Login or Register  to view this content.