Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Texttocolumns and Date Format using Macro - UK International setti
Would appreciate any help on this one. Using Excel 2003 / Windows XP SP2.
I import information from a database - this includes dates that are in MM/DD/YYYY format. As I am in the UK, Excel's auto-features convert some of the dates correctly, others incorrectly. I therefore use the data/texttocolumns function to give correct dates - have done this many times. I have just tried to record a macro using these steps (remembering to select the MDY option for the date format). The result was perfect when recording. However, when running the macro on fresh data, it failed - an example is a data that when imported was 5/1/2007 (1 May 2007, US format) and when manually changed using text to columns shows as 1/5/2007 (1 May 2007, UK format). converting using the recorded macro gives the result 5/1/2007 (5 Jan 2007, UK format) I suspect this may be caused in some way by the International Date settings being ignored by the macro, but used in manual activity. However I have no idea how to change the behaviour within a macro. I could re-write the whole macro so that I parse each cell and assign the values to the correct date component, but that would be much of a sledge-hammer to crack a nut. Thanks for reading this - hope you can help me. -- KenY |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Texttocolumns and Date Format using Macro - UK International setti
Can you just do the date formatting in the SQL before it arrives in Excel?
Tim "KenY" wrote in message ... Would appreciate any help on this one. Using Excel 2003 / Windows XP SP2. I import information from a database - this includes dates that are in MM/DD/YYYY format. As I am in the UK, Excel's auto-features convert some of the dates correctly, others incorrectly. I therefore use the data/texttocolumns function to give correct dates - have done this many times. I have just tried to record a macro using these steps (remembering to select the MDY option for the date format). The result was perfect when recording. However, when running the macro on fresh data, it failed - an example is a data that when imported was 5/1/2007 (1 May 2007, US format) and when manually changed using text to columns shows as 1/5/2007 (1 May 2007, UK format). converting using the recorded macro gives the result 5/1/2007 (5 Jan 2007, UK format) I suspect this may be caused in some way by the International Date settings being ignored by the macro, but used in manual activity. However I have no idea how to change the behaviour within a macro. I could re-write the whole macro so that I parse each cell and assign the values to the correct date component, but that would be much of a sledge-hammer to crack a nut. Thanks for reading this - hope you can help me. -- KenY |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Texttocolumns and Date Format using Macro - UK International s
Unfortunately, no. The system I am extracting from is used globally, so the
extract/report would need to be different for each country format. -- KenY "Tim Williams" wrote: Can you just do the date formatting in the SQL before it arrives in Excel? Tim "KenY" wrote in message ... Would appreciate any help on this one. Using Excel 2003 / Windows XP SP2. I import information from a database - this includes dates that are in MM/DD/YYYY format. As I am in the UK, Excel's auto-features convert some of the dates correctly, others incorrectly. I therefore use the data/texttocolumns function to give correct dates - have done this many times. I have just tried to record a macro using these steps (remembering to select the MDY option for the date format). The result was perfect when recording. However, when running the macro on fresh data, it failed - an example is a data that when imported was 5/1/2007 (1 May 2007, US format) and when manually changed using text to columns shows as 1/5/2007 (1 May 2007, UK format). converting using the recorded macro gives the result 5/1/2007 (5 Jan 2007, UK format) I suspect this may be caused in some way by the International Date settings being ignored by the macro, but used in manual activity. However I have no idea how to change the behaviour within a macro. I could re-write the whole macro so that I parse each cell and assign the values to the correct date component, but that would be much of a sledge-hammer to crack a nut. Thanks for reading this - hope you can help me. -- KenY |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Texttocolumns and Date Format using Macro - UK International s
How are you performing the import?
Tim "KenY" wrote in message ... Unfortunately, no. The system I am extracting from is used globally, so the extract/report would need to be different for each country format. -- KenY "Tim Williams" wrote: Can you just do the date formatting in the SQL before it arrives in Excel? Tim "KenY" wrote in message ... Would appreciate any help on this one. Using Excel 2003 / Windows XP SP2. I import information from a database - this includes dates that are in MM/DD/YYYY format. As I am in the UK, Excel's auto-features convert some of the dates correctly, others incorrectly. I therefore use the data/texttocolumns function to give correct dates - have done this many times. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Texttocolumns and Date Format using Macro - UK International s
Tim
Sorry for the delay - I have been on holiday for the past week. The main database has a browser based report function. Once displayed in the browser, the report is saved as a *.htm file. This is then pulled into excel using a macro to clean up formatting issues (mainly merged cells). I am pretty sure that the dates would not be an issue if the instance of excel was running in US mode, it is just that my excel is in UK mode. Do you know of any variables that can be set/reset within a macro to temporarily set the programme in US mode? Thanks -- KenY "Tim Williams" wrote: How are you performing the import? Tim "KenY" wrote in message ... Unfortunately, no. The system I am extracting from is used globally, so the extract/report would need to be different for each country format. -- KenY "Tim Williams" wrote: Can you just do the date formatting in the SQL before it arrives in Excel? Tim "KenY" wrote in message ... Would appreciate any help on this one. Using Excel 2003 / Windows XP SP2. I import information from a database - this includes dates that are in MM/DD/YYYY format. As I am in the UK, Excel's auto-features convert some of the dates correctly, others incorrectly. I therefore use the data/texttocolumns function to give correct dates - have done this many times. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Texttocolumns and Date Format using Macro - UK International s
Do you know of any variables that can be set/reset within a macro
to temporarily set the programme in US mode? That would mean changing system date settings to US style, I think. Maybe you could try something like this - Sub Test2() Dim s As String Dim dt As Date Dim vSplit s = "03/06/07" dt = CDate(s) Debug.Print Format(dt, "dd mmm yyyy") '03 Jun 2007 in Int-date system ' parse the date, this is just one way which requires error handling (not shown) vSplit = Split(s, "/") dt = DateSerial(vSplit(2), vSplit(0), vSplit(1)) Debug.Print Format(dt, "dd mmm yyyy") '06 Mar 2007 End Sub "KenY" wrote in message ... Tim Sorry for the delay - I have been on holiday for the past week. The main database has a browser based report function. Once displayed in the browser, the report is saved as a *.htm file. This is then pulled into excel using a macro to clean up formatting issues (mainly merged cells). I am pretty sure that the dates would not be an issue if the instance of excel was running in US mode, it is just that my excel is in UK mode. Do you know of any variables that can be set/reset within a macro to temporarily set the programme in US mode? Thanks -- KenY "Tim Williams" wrote: How are you performing the import? Tim "KenY" wrote in message ... Unfortunately, no. The system I am extracting from is used globally, so the extract/report would need to be different for each country format. -- KenY "Tim Williams" wrote: Can you just do the date formatting in the SQL before it arrives in Excel? Tim "KenY" wrote in message ... Would appreciate any help on this one. Using Excel 2003 / Windows XP SP2. I import information from a database - this includes dates that are in MM/DD/YYYY format. As I am in the UK, Excel's auto-features convert some of the dates correctly, others incorrectly. I therefore use the data/texttocolumns function to give correct dates - have done this many times. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Texttocolumns and Date Format using Macro - UK International s
Peter
Thanks for the suggestion - guess there is no simple fix to this inconsistent behaviour of texttocolumns (user vs macro initiated). As you indicate, this will involve a fair bit more coding for recalculate each populated cell in the column and to cope with error conditions. -- KenY "Peter T" wrote: Do you know of any variables that can be set/reset within a macro to temporarily set the programme in US mode? That would mean changing system date settings to US style, I think. Maybe you could try something like this - Sub Test2() Dim s As String Dim dt As Date Dim vSplit s = "03/06/07" dt = CDate(s) Debug.Print Format(dt, "dd mmm yyyy") '03 Jun 2007 in Int-date system ' parse the date, this is just one way which requires error handling (not shown) vSplit = Split(s, "/") dt = DateSerial(vSplit(2), vSplit(0), vSplit(1)) Debug.Print Format(dt, "dd mmm yyyy") '06 Mar 2007 End Sub "KenY" wrote in message ... Tim Sorry for the delay - I have been on holiday for the past week. The main database has a browser based report function. Once displayed in the browser, the report is saved as a *.htm file. This is then pulled into excel using a macro to clean up formatting issues (mainly merged cells). I am pretty sure that the dates would not be an issue if the instance of excel was running in US mode, it is just that my excel is in UK mode. Do you know of any variables that can be set/reset within a macro to temporarily set the programme in US mode? Thanks -- KenY "Tim Williams" wrote: How are you performing the import? Tim "KenY" wrote in message ... Unfortunately, no. The system I am extracting from is used globally, so the extract/report would need to be different for each country format. -- KenY "Tim Williams" wrote: Can you just do the date formatting in the SQL before it arrives in Excel? Tim "KenY" wrote in message ... Would appreciate any help on this one. Using Excel 2003 / Windows XP SP2. I import information from a database - this includes dates that are in MM/DD/YYYY format. As I am in the UK, Excel's auto-features convert some of the dates correctly, others incorrectly. I therefore use the data/texttocolumns function to give correct dates - have done this many times. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how format date with TEXT() function for international usage? | Excel Worksheet Functions | |||
Working with times in military/international format. | Excel Discussion (Misc queries) | |||
Macro Problem TextToColumns | Excel Programming | |||
Format function with international numbers | Excel Programming | |||
date format in excel not in line with control panel regional setti | Excel Discussion (Misc queries) |