Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how format date with TEXT() function for international usage? | Excel Worksheet Functions | |||
Date Conversion | Excel Discussion (Misc queries) | |||
Date Conversion | Excel Discussion (Misc queries) | |||
Losing date values upon conversion from EXCEL to CSV | Excel Discussion (Misc queries) | |||
date conversion | Excel Worksheet Functions |