How to prevent change Numeric value to date format ?

[moonhk]

Hi Reader

How to prevent change Numeric value to date format ?

Add Not IsNumeric(Selection.Item(ir, ic).Value) not work.

Sub ChangeYYYYMMDD()
Dim iRows As Long
Dim iColumns As Long
Dim ir As Long
Dim ic As Long
iRows = Selection.Rows.Count
iColumns = Selection.Columns.Count
For ir = 1 To iRows
For ic = 1 To iColumns
If IsDate(Selection.Item(ir, ic).Value) And Not
IsNumeric(Selection.Item(ir, ic).Value) Then
Selection.NumberFormatLocal = "yyyy/mm/dd"
End If
Next ic
Next ir
End Sub

 

[Joel]

You have to understand what date format actually is. It is a number. Date
is the number of days from Jan 1, 1900. So the date is around 39,000. A
date will pass a numeric test.

You may be able to test for the date being a range from a start date to an
end date such as

mydate > datevalue("1/1/2000") and mydate < datevalue("12/12/2010")

 

[moonhk]

You have to understand what date format actually is. It is a number. Date
is the number of days from Jan 1, 1900. So the date is around 39,000. A
date will pass a numeric test.

You may be able to test for the date being a range from a start date to an
end date such as

mydate > datevalue("1/1/2000") and mydate < datevalue("12/12/2010")








- $Bp}<(Ho0zMQJ8;z(B -

Thank. I just using VarType to test the cell type.

Sub ChangeYYYYMMDD()
'~~ 2007/12/07 moonhk
Dim iRows As Long
Dim iColumns As Long
Dim ir As Long
Dim ic As Long
Dim k
iRows = Selection.Rows.Count
iColumns = Selection.Columns.Count
For ir = 1 To iRows
For ic = 1 To iColumns
If VarType(Selection.Item(ir, ic)) = vbDate Then
Selection.Item(ir, ic).NumberFormatLocal = "yyyy/mm/dd"
End If
Next ic
Next ir
End Sub

 

[Joel]

Not sure. You can use number formatt to return the way a cell is formatted.
the problem is dates can be formated differrent ways and number can be
formatted diferent ways. therefore, you have a lot of testing you may have
to do.