numeric value only , no dates input allowed

[surk]

I need to somehow have a whole numeric value in a cell (but not allow a date input). The numeric value can be anything from 0-6 digits long. (i.e. can i stop the following symbols being put in to prevent this . / - ?

[etaf]

EDIT
Ignore as it accepts the number as a date -
you can use data validation so that only numbers can be entered
https://www.techonthenet.com/excel/c...tion2_2010.php

[MrShorty]

As etaf notes, simple "restrict entry to a number" won't work, because Excel sees dates as numbers. The only difference between a number and a date is the cell formatting.

I tried using data validation using the CELL() function to return formatting information (https://support.office.com/en-us/art...f-955d67c2b2cf ).
(Data Validation -> Settings -> Allow custom -> formula =LEFT(CELL("format"),1)<>"D"). This mostly worked, as long as the cell was preformatted as "General". When the cell is preformatted as "General" and a date and/or time entry is attempted, then Excel will automatically format the cell with an appropriate date format. If I had the cell preformatted as "0" ("F0" as the CELL() function saw it), then Excel would receive the date entry, convert it to the appropriate serial number, and enter that number into the cell without changing the format. So an entry of 1/13/16 would become 42382 (which fits very nicely within your 0 to 1E7 data entry range).

I could also figuring out an approach that preformats the cell as text "@", so that Excel will not try to interpret any input. Then you can test the input in other ways. For example, I preformatted a cell as text "@", data validation -> settings -> allow custom -> formula =IFERROR(FIND("/",A1),-1)<0. This would allow anything to be entered in the cell except for something containing a "/". As long as you are certain that no one will ever try to use any other date format delimiter (eg. 1.13.16), then this should also catch the invalid entry.

Not perfect solutions, but maybe they are workable or will provide a starting place for what you are trying to do.

[surk]

I have tried this, and I it works for any date as you say with a " / " divider. So I will use that, its more of a case of a buffer in case its input wrongly, and wonder if you can expand the formula, but will experiment, but it does nicely as it is! Many thanks

I could also figuring out an approach that preformats the cell as text "@", so that Excel will not try to interpret any input. Then you can test the input in other ways. For example, I preformatted a cell as text "@", data validation -> settings -> allow custom -> formula =IFERROR(FIND("/",A1),-1)<0. This would allow anything to be entered in the cell except for something containing a "/". As long as you are certain that no one will ever try to use any other date format delimiter (eg. 1.13.16), then this should also catch the invalid entry.

Not perfect solutions, but maybe they are workable or will provide a starting place for what you are trying to do.