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

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" wrote:

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

On 12$B7n(B7$BF|(B, $Be8a(B11$B;~(B28$BJ,(B, Joel wrote:
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" wrote:
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- $Bp,i6Ho0zMQJ8;z(B -

- $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

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.

"moonhk" wrote:

On 12月7日, 上�11時28分, Joel wrote:
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" wrote:
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- éš±è—�被引用文å*— -

- 顯示被引用文å*— -

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