Numeric value only in a cell (no dates)

[surkdidat]

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 . / - ?

 

[chicagocomputerclasses]

You can use LEFT(CELL("format",A1))<>"D" to make sure it's not a date.

 

[surkdidat]

Hi there -if i enter a date such as 13/12/16 it accepts this though...

You can use LEFT(CELL("format",A1))<>"D" to make sure it's not a date.

 

[chicagocomputerclasses]

Yes, it's an invalid date, so it will be treated as text.

You want to use AND() function to handle the rest, something like AND(ISNUMBER(A1),LEFT(CELL("format",A1))<>"D",OTHER_CONDITIONS)
In other conditions you can require other things, like character min max length, make sure it's a whole number, etc., just separate each with a comma in your AND function.

 

[surkdidat]

Thanks for your help - where I am new to this can you help me with the second part of the formula to.

- Need to length to be between 0 and 6 characters
- Only whole numbers
- No alpha or special characters

Yes, it's an invalid date, so it will be treated as text.

You want to use AND() function to handle the rest, something like AND(ISNUMBER(A1),LEFT(CELL("format",A1))<>"D",OTHER_CONDITIONS)
In other conditions you can require other things, like character min max length, make sure it's a whole number, etc., just separate each with a comma in your AND function.

 

[chicagocomputerclasses]

Thanks for your help - where I am new to this can you help me with the second part of the formula to.

- Need to length to be between 0 and 6 characters
- Only whole numbers
- No alpha or special characters

Try this:

=AND(ISNUMBER(A1),LEFT(CELL("format",A1))<>"D",MOD(A1,1)=0,LEN(A1)<7)

 

[mikerickson]

Data Validation will restrict user entry to the cell to values between 100000 and 999999.

All that one needs to do at that point is insure that the cell remains in general format and doesn't automatically change to a date format. You can do that with this change event.

Private Sub Worksheet_Change(ByVal Target As Range)
    Target.NumberFormat = ""
End Sub

 

[surkdidat]

I have found a nice little workaround, which is as follows :


I have preformatted the cell as text "@", so that Excel will not try to interpret any input.

Then using data validation , I have input the following as a custom formula :

 =IFERROR(FIND("/",A1),-1)<0    

Data Validation will restrict user entry to the cell to values between 100000 and 999999.

All that one needs to do at that point is insure that the cell remains in general format and doesn't automatically change to a date format. You can do that with this change event.

Private Sub Worksheet_Change(ByVal Target As Range)
    Target.NumberFormat = ""
End Sub