Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date format
I copy data from a Word document into Excel. I then run a macro to arrange
this data in the correct format for users. One of the column's contains dates, but instead of the / separator they are separated by a . Eg. 31.12.02 02.10.02 I use the following to replace them: Range("F2:F" & Range("F65536").End(XlUp).Row).Replace ".","/" And get: 31/12/02 10/02/2002 Is it something to do with the US date format or am I missing something really easy? Thanks in advance. Gareth NB My PC date settings are dd/mm/yyyy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date format
Yes. Generally if a data can be interpreted as a valid date using a US
format interpretation, it will be interpreted that way. cdate is supposed to observe your regional settings and it appears to accept period as a separator. try cdate("31.12.02") cdate("02.10.02") -- Regards, Tom Ogilvy Gareth wrote in message ... I copy data from a Word document into Excel. I then run a macro to arrange this data in the correct format for users. One of the column's contains dates, but instead of the / separator they are separated by a . Eg. 31.12.02 02.10.02 I use the following to replace them: Range("F2:F" & Range("F65536").End(XlUp).Row).Replace ".","/" And get: 31/12/02 10/02/2002 Is it something to do with the US date format or am I missing something really easy? Thanks in advance. Gareth NB My PC date settings are dd/mm/yyyy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date format
thanks Tom, problem is the column has several 100 dates, is it possible to
CDate them in one go? Gareth "Tom Ogilvy" wrote in message ... Yes. Generally if a data can be interpreted as a valid date using a US format interpretation, it will be interpreted that way. cdate is supposed to observe your regional settings and it appears to accept period as a separator. try cdate("31.12.02") cdate("02.10.02") -- Regards, Tom Ogilvy Gareth wrote in message ... I copy data from a Word document into Excel. I then run a macro to arrange this data in the correct format for users. One of the column's contains dates, but instead of the / separator they are separated by a . Eg. 31.12.02 02.10.02 I use the following to replace them: Range("F2:F" & Range("F65536").End(XlUp).Row).Replace ".","/" And get: 31/12/02 10/02/2002 Is it something to do with the US date format or am I missing something really easy? Thanks in advance. Gareth NB My PC date settings are dd/mm/yyyy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date format
No.
You would have to loop through them. If you do the replace manually, you shouldn't have the problem. If you need a macro, try using sendkeys to execute the replacement and you probably won't have the problem. -- Regards, Tom Ogilvy Gareth wrote in message ... thanks Tom, problem is the column has several 100 dates, is it possible to CDate them in one go? Gareth "Tom Ogilvy" wrote in message ... Yes. Generally if a data can be interpreted as a valid date using a US format interpretation, it will be interpreted that way. cdate is supposed to observe your regional settings and it appears to accept period as a separator. try cdate("31.12.02") cdate("02.10.02") -- Regards, Tom Ogilvy Gareth wrote in message ... I copy data from a Word document into Excel. I then run a macro to arrange this data in the correct format for users. One of the column's contains dates, but instead of the / separator they are separated by a . Eg. 31.12.02 02.10.02 I use the following to replace them: Range("F2:F" & Range("F65536").End(XlUp).Row).Replace ".","/" And get: 31/12/02 10/02/2002 Is it something to do with the US date format or am I missing something really easy? Thanks in advance. Gareth NB My PC date settings are dd/mm/yyyy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date format
On Sun, 18 Jan 2004 20:17:01 -0000, "Gareth"
wrote: I copy data from a Word document into Excel. I then run a macro to arrange this data in the correct format for users. One of the column's contains dates, but instead of the / separator they are separated by a . Eg. 31.12.02 02.10.02 I use the following to replace them: Range("F2:F" & Range("F65536").End(XlUp).Row).Replace ".","/" And get: 31/12/02 10/02/2002 Is it something to do with the US date format or am I missing something really easy? Thanks in advance. Gareth NB How about: ===================== Range("F2:F" & Range("F65536").End(XlUp).Row).TextToColumns _ Destination:=Range("F2"), _ DataType:=xlFixedWidth, _ FieldInfo:=Array(0, xlDMYFormat) ===================== --ron |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date format
Never heard of/used SendKeys. How would it work in this
situation? Gareth -----Original Message----- No. You would have to loop through them. If you do the replace manually, you shouldn't have the problem. If you need a macro, try using sendkeys to execute the replacement and you probably won't have the problem. -- Regards, Tom Ogilvy Gareth wrote in message ... thanks Tom, problem is the column has several 100 dates, is it possible to CDate them in one go? Gareth "Tom Ogilvy" wrote in message ... Yes. Generally if a data can be interpreted as a valid date using a US format interpretation, it will be interpreted that way. cdate is supposed to observe your regional settings and it appears to accept period as a separator. try cdate("31.12.02") cdate("02.10.02") -- Regards, Tom Ogilvy Gareth wrote in message ... I copy data from a Word document into Excel. I then run a macro to arrange this data in the correct format for users. One of the column's contains dates, but instead of the / separator they are separated by a . Eg. 31.12.02 02.10.02 I use the following to replace them: Range("F2:F" & Range("F65536").End (XlUp).Row).Replace ".","/" And get: 31/12/02 10/02/2002 Is it something to do with the US date format or am I missing something really easy? Thanks in advance. Gareth NB My PC date settings are dd/mm/yyyy . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
permanent conversion of 1904 date format to 1900 date format | Excel Worksheet Functions | |||
change date format dd/mm/yyyy to Julian date format? | Excel Worksheet Functions | |||
Convert date + time text format to date format | Excel Worksheet Functions | |||
Excel 2000 date format cannot be set to Australian date format | Excel Discussion (Misc queries) | |||
Change a date in text format xx.xx.20xx to a recognised date format | Excel Programming |