International Date Conversion to Values
 

Hello,

I have been trying to program in VB converting International dates to values.

2. The original dates are formatted as general with a blank character at the
beginning of the date " 01/06/2006". I got the VB running to eliminate the
blank character.

3. I got the vb running to convert the International date to English
06/01/2006. However ,the dates passed 12/06/2006 (e.g 13/06/2006) remains in
this format and also has a general format and does not convert to values..

Example of Dates

01/06/2006
10/06/2006
12/06/2006
13/06/2006
14/06/2006


Any help will be greatly appreciated.

Thanks.


Ligaya

International Date Conversion to Values
 

use cdate to do the conversion. It will utilize teh English format if that
is what your regional settings are.

for each cell in selection
cell.value = cdate(trim(cell.Value))
cell.Numberformat = "dd/mm/yyyy"
Next


--
Regards,
Tom Ogilvy


"Ligaya" wrote:

Hello,

I have been trying to program in VB converting International dates to values.

2. The original dates are formatted as general with a blank character at the
beginning of the date " 01/06/2006". I got the VB running to eliminate the
blank character.

3. I got the vb running to convert the International date to English
06/01/2006. However ,the dates passed 12/06/2006 (e.g 13/06/2006) remains in
this format and also has a general format and does not convert to values..

Example of Dates

01/06/2006
10/06/2006
12/06/2006
13/06/2006
14/06/2006


Any help will be greatly appreciated.

Thanks.


Ligaya

International Date Conversion to Values
 

Good morning Tom.

Thanks for your prompt response. I tried your suggestions. I still get
incorrect values for 01/06/2006 to 12/06/2006.

My Codes a

Sub Convert()
'
' Convert International Date from General (Format " 13/106/2006") to date
values
'
Dim Cell As Variant
ActiveSheet.Select
Range("b2:b22").Select
For Each Cell In Selection
Cell.Value = CDate(Trim(Cell.Value))
Cell.NumberFormat = "mm/dd/yy"
Next
ActiveSheet.Select
Range("b2:b22").Select
Selection.Copy
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

Results

Original Data Converted Values
01/06/2006 01/06/06 38723
02/06/2006 02/06/06 38754
06/06/2006 06/06/06 38874
07/06/2006 07/06/06 38904
08/06/2006 08/06/06 38935
09/06/2006 09/06/06 38966
12/06/2006 12/06/06 39057
13/06/2006 06/13/06 38881
14/06/2006 06/14/06 38882
15/06/2006 06/15/06 38883
16/06/2006 06/16/06 38884
19/06/2006 06/19/06 38887
20/06/2006 06/20/06 38888
21/06/2006 06/21/06 38889
22/06/2006 06/22/06 38890
23/06/2006 06/23/06 38891
26/06/2006 06/26/06 38894
27/06/2006 06/27/06 38895
28/06/2006 06/28/06 38896
29/06/2006 06/29/06 38897
30/06/2006 06/30/06 38898

What am I missing.

Again many thanks for your help.

Ligaya

"Tom Ogilvy" wrote:

use cdate to do the conversion. It will utilize teh English format if that
is what your regional settings are.

for each cell in selection
cell.value = cdate(trim(cell.Value))
cell.Numberformat = "dd/mm/yyyy"
Next


--
Regards,
Tom Ogilvy


"Ligaya" wrote:

Hello,

I have been trying to program in VB converting International dates to values.

2. The original dates are formatted as general with a blank character at the
beginning of the date " 01/06/2006". I got the VB running to eliminate the
blank character.

3. I got the vb running to convert the International date to English
06/01/2006. However ,the dates passed 12/06/2006 (e.g 13/06/2006) remains in
this format and also has a general format and does not convert to values..

Example of Dates

01/06/2006
10/06/2006
12/06/2006
13/06/2006
14/06/2006


Any help will be greatly appreciated.

Thanks.


Ligaya