Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does the date toggle between formats
I have the following code that updates a sheet on change to convert (fix)
dates to DMY. Occassionally the sheet will be updated again if the user makes a second update (an infrequent event). On the second update the format will change to MDY. I tested this by repeatedly running the macro and can see the dates toggle back and forth. Does anyone have any idea why or how I can fix it to DMY no matter how many times it's activated? Dim ColsToFix As Variant Dim TypeOfCols As Variant Dim iCol As Long ColsToFix = Array("G", "E", "C", "K", "M", "Q", "R", "S", "U", "W") TypeOfCols = Array(4, 1, 1, 1, 1, 1, 1, 1, 1, 1) If UBound(TypeOfCols) < UBound(ColsToFix) Then MsgBox "design error!-Cols & Types not matched" Exit Sub End If With ActiveSheet For iCol = LBound(ColsToFix) To UBound(ColsToFix) .Cells(1, ColsToFix(iCol)).EntireColumn.TextToColumns _ DataType:=xlDelimited, _ FieldInfo:=Array(1, TypeOfCols(iCol)) Next iCol End With -- Jim |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does the date toggle between formats
Hi Jim,
I have looked at this for ages and maybe I am missing something. You say it fixes the dates and I interpret this to mean in all columns. The following array:- TypeOfCols = Array(4, 1, 1, 1, 1, 1, 1, 1, 1, 1) only has the first element populated with a 4 for DMY dates so I should think that only the first column is coerced to recognise the data as a date. What I really do not understand is what is it about the data in each column that you can not simply format the columns to "d/m/yy" date format because it appears that you are using text to columns on each individual column. Regards, OssieMac "Jim G" wrote: I have the following code that updates a sheet on change to convert (fix) dates to DMY. Occassionally the sheet will be updated again if the user makes a second update (an infrequent event). On the second update the format will change to MDY. I tested this by repeatedly running the macro and can see the dates toggle back and forth. Does anyone have any idea why or how I can fix it to DMY no matter how many times it's activated? Dim ColsToFix As Variant Dim TypeOfCols As Variant Dim iCol As Long ColsToFix = Array("G", "E", "C", "K", "M", "Q", "R", "S", "U", "W") TypeOfCols = Array(4, 1, 1, 1, 1, 1, 1, 1, 1, 1) If UBound(TypeOfCols) < UBound(ColsToFix) Then MsgBox "design error!-Cols & Types not matched" Exit Sub End If With ActiveSheet For iCol = LBound(ColsToFix) To UBound(ColsToFix) .Cells(1, ColsToFix(iCol)).EntireColumn.TextToColumns _ DataType:=xlDelimited, _ FieldInfo:=Array(1, TypeOfCols(iCol)) Next iCol End With -- Jim |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does the date toggle between formats
Ossiemac,
Sorry I missed your reply, I don't seem to be getting notifications. I have data that comes from an accounting/job costing system that generates Excel files. Unfortunately, the data can randomly change type a few hundred lines into the data. I changed the the date format in the accounting software to dd.mm.yy to avoid ambiguity but some dates still change to MDY for those that can resolve while others don't. For example, 12 Sept will change to 9 Dec while 24 Sept stays the same. Some of the numerical columns will change to text. It was easier for me to coerce the colums I needed for calculations on every instance. While this piece of code works fine when only run once, I occassionally need to make a change to the data sheet (add a digit to the job number to prevent overwritting the original when saved). I just can't work out why it toggles between formats while a macro recording keystrokes to convert a single column dosen't. -- Jim "OssieMac" wrote: Hi Jim, I have looked at this for ages and maybe I am missing something. You say it fixes the dates and I interpret this to mean in all columns. The following array:- TypeOfCols = Array(4, 1, 1, 1, 1, 1, 1, 1, 1, 1) only has the first element populated with a 4 for DMY dates so I should think that only the first column is coerced to recognise the data as a date. What I really do not understand is what is it about the data in each column that you can not simply format the columns to "d/m/yy" date format because it appears that you are using text to columns on each individual column. Regards, OssieMac "Jim G" wrote: I have the following code that updates a sheet on change to convert (fix) dates to DMY. Occassionally the sheet will be updated again if the user makes a second update (an infrequent event). On the second update the format will change to MDY. I tested this by repeatedly running the macro and can see the dates toggle back and forth. Does anyone have any idea why or how I can fix it to DMY no matter how many times it's activated? Dim ColsToFix As Variant Dim TypeOfCols As Variant Dim iCol As Long ColsToFix = Array("G", "E", "C", "K", "M", "Q", "R", "S", "U", "W") TypeOfCols = Array(4, 1, 1, 1, 1, 1, 1, 1, 1, 1) If UBound(TypeOfCols) < UBound(ColsToFix) Then MsgBox "design error!-Cols & Types not matched" Exit Sub End If With ActiveSheet For iCol = LBound(ColsToFix) To UBound(ColsToFix) .Cells(1, ColsToFix(iCol)).EntireColumn.TextToColumns _ DataType:=xlDelimited, _ FieldInfo:=Array(1, TypeOfCols(iCol)) Next iCol End With -- Jim |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does the date toggle between formats
On Mon, 29 Oct 2007 17:31:00 -0700, Jim G
wrote: I have data that comes from an accounting/job costing system that generates Excel files. Unfortunately, the data can randomly change type a few hundred lines into the data. I changed the the date format in the accounting software to dd.mm.yy to avoid ambiguity but some dates still change to MDY for those that can resolve while others don't. For example, 12 Sept will change to 9 Dec while 24 Sept stays the same. Some of the numerical columns will change to text. I cannot be sure, but I believe your problem is likely due to the formatting of the accounting program as it gets interpreted by Excel. You write that the date format in your accounting program is dd.mm.yy. For me, that would mean that 12 Sep 2007 would be coming out of your accounting program as 12.09.07 -- not as 12 Sept That being the case, Excel will interpret your date in accord with the short date format of your Windows Regional Settings. (Control Panel/Regional Settings, or something similar) If your Windows Regional settings are set to US, for example: 12.09.07 -- 9 Dec 2007 24.09.07 is not interpretable by Excel, so it remains as a text string. But the Data/Text to Columns wizard, expecting a date in the format of dd.mm.yy IS able to interpret that text string, and does so as 24 Sep 2007. What you need to do, I believe, is to change the accounting date format generator to match the format of your Windows Regional Settings. (Or vice versa). --ron |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does the date toggle between formats
Hi Jim,
I often miss replies in this forum and I am sure that I always check the box saying that I want repies. Anyway your problem. The best that I can interpret your code, the TypeOfCols array tells the text to columns what sort of data is in each column. Value 4 tells it that the column contains DMY date format. Value 1 tells it to treat the column as general (and to guess what sort of data the column contains). MDY is the priority guess if it looks like it might be a date and then it assumes that 12/9/07 is Dec 9 2007. However, any value that looks like a date and the day of the month is greater than 12 (like 24/9/07) then it guesses correctly. Therefore my question to you is which of the following columns have dates? ColsToFix = Array("G", "E", "C", "K", "M", "Q", "R", "S", "U", "W") TypeOfCols array values should be 4 instead of 1 for each column with dates. currently only column G has a corresponding value of 4 for dates. TypeOfCols = Array(4, 1, 1, 1, 1, 1, 1, 1, 1, 1) I will be interested in knowing if this is the answer or is column G the only column with dates and this is the problem? Regards, OssieMac "Jim G" wrote: Ossiemac, Sorry I missed your reply, I don't seem to be getting notifications. I have data that comes from an accounting/job costing system that generates Excel files. Unfortunately, the data can randomly change type a few hundred lines into the data. I changed the the date format in the accounting software to dd.mm.yy to avoid ambiguity but some dates still change to MDY for those that can resolve while others don't. For example, 12 Sept will change to 9 Dec while 24 Sept stays the same. Some of the numerical columns will change to text. It was easier for me to coerce the colums I needed for calculations on every instance. While this piece of code works fine when only run once, I occassionally need to make a change to the data sheet (add a digit to the job number to prevent overwritting the original when saved). I just can't work out why it toggles between formats while a macro recording keystrokes to convert a single column dosen't. -- Jim "OssieMac" wrote: Hi Jim, I have looked at this for ages and maybe I am missing something. You say it fixes the dates and I interpret this to mean in all columns. The following array:- TypeOfCols = Array(4, 1, 1, 1, 1, 1, 1, 1, 1, 1) only has the first element populated with a 4 for DMY dates so I should think that only the first column is coerced to recognise the data as a date. What I really do not understand is what is it about the data in each column that you can not simply format the columns to "d/m/yy" date format because it appears that you are using text to columns on each individual column. Regards, OssieMac "Jim G" wrote: I have the following code that updates a sheet on change to convert (fix) dates to DMY. Occassionally the sheet will be updated again if the user makes a second update (an infrequent event). On the second update the format will change to MDY. I tested this by repeatedly running the macro and can see the dates toggle back and forth. Does anyone have any idea why or how I can fix it to DMY no matter how many times it's activated? Dim ColsToFix As Variant Dim TypeOfCols As Variant Dim iCol As Long ColsToFix = Array("G", "E", "C", "K", "M", "Q", "R", "S", "U", "W") TypeOfCols = Array(4, 1, 1, 1, 1, 1, 1, 1, 1, 1) If UBound(TypeOfCols) < UBound(ColsToFix) Then MsgBox "design error!-Cols & Types not matched" Exit Sub End If With ActiveSheet For iCol = LBound(ColsToFix) To UBound(ColsToFix) .Cells(1, ColsToFix(iCol)).EntireColumn.TextToColumns _ DataType:=xlDelimited, _ FieldInfo:=Array(1, TypeOfCols(iCol)) Next iCol End With -- Jim |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does the date toggle between formats
A great piece of reasoning OssieMac.
The accounting system generates the date as 12.09.07 ( Ron, I mentioned 12 Sept so there was no confusion here that it might be Dec). The columns all have a mixture of values, dates and text. Golumn G has the only dates used in calculation. I use the formula =INDEX(JobCard!G:G,MATCH(MAX(JobCard!G26:G1816),Jo bCard!G:G,0)) to return the latest date. If I don't convert Col G to date it returns 0/01/1900. All other dates are okay reported as text dd.mm.yy. When the dates were formated by the accounting system as d/m/y some of them, as mentioned, changed to m/d/y. A clear inconsistency in the accounting system (ironically the system is also Microsoft). The macro was intened to coerce d/m/y on each new set of data. Interestingly, the values in column G don't revert to dates when I run the macro. The values are more important than the dates, so if there isn't a better way, I'll just have to run the macro twice when I make a non-routine change to the data. -- Jim "OssieMac" wrote: Hi Jim, I often miss replies in this forum and I am sure that I always check the box saying that I want repies. Anyway your problem. The best that I can interpret your code, the TypeOfCols array tells the text to columns what sort of data is in each column. Value 4 tells it that the column contains DMY date format. Value 1 tells it to treat the column as general (and to guess what sort of data the column contains). MDY is the priority guess if it looks like it might be a date and then it assumes that 12/9/07 is Dec 9 2007. However, any value that looks like a date and the day of the month is greater than 12 (like 24/9/07) then it guesses correctly. Therefore my question to you is which of the following columns have dates? ColsToFix = Array("G", "E", "C", "K", "M", "Q", "R", "S", "U", "W") TypeOfCols array values should be 4 instead of 1 for each column with dates. currently only column G has a corresponding value of 4 for dates. TypeOfCols = Array(4, 1, 1, 1, 1, 1, 1, 1, 1, 1) I will be interested in knowing if this is the answer or is column G the only column with dates and this is the problem? Regards, OssieMac "Jim G" wrote: Ossiemac, Sorry I missed your reply, I don't seem to be getting notifications. I have data that comes from an accounting/job costing system that generates Excel files. Unfortunately, the data can randomly change type a few hundred lines into the data. I changed the the date format in the accounting software to dd.mm.yy to avoid ambiguity but some dates still change to MDY for those that can resolve while others don't. For example, 12 Sept will change to 9 Dec while 24 Sept stays the same. Some of the numerical columns will change to text. It was easier for me to coerce the colums I needed for calculations on every instance. While this piece of code works fine when only run once, I occassionally need to make a change to the data sheet (add a digit to the job number to prevent overwritting the original when saved). I just can't work out why it toggles between formats while a macro recording keystrokes to convert a single column dosen't. -- Jim "OssieMac" wrote: Hi Jim, I have looked at this for ages and maybe I am missing something. You say it fixes the dates and I interpret this to mean in all columns. The following array:- TypeOfCols = Array(4, 1, 1, 1, 1, 1, 1, 1, 1, 1) only has the first element populated with a 4 for DMY dates so I should think that only the first column is coerced to recognise the data as a date. What I really do not understand is what is it about the data in each column that you can not simply format the columns to "d/m/yy" date format because it appears that you are using text to columns on each individual column. Regards, OssieMac "Jim G" wrote: I have the following code that updates a sheet on change to convert (fix) dates to DMY. Occassionally the sheet will be updated again if the user makes a second update (an infrequent event). On the second update the format will change to MDY. I tested this by repeatedly running the macro and can see the dates toggle back and forth. Does anyone have any idea why or how I can fix it to DMY no matter how many times it's activated? Dim ColsToFix As Variant Dim TypeOfCols As Variant Dim iCol As Long ColsToFix = Array("G", "E", "C", "K", "M", "Q", "R", "S", "U", "W") TypeOfCols = Array(4, 1, 1, 1, 1, 1, 1, 1, 1, 1) If UBound(TypeOfCols) < UBound(ColsToFix) Then MsgBox "design error!-Cols & Types not matched" Exit Sub End If With ActiveSheet For iCol = LBound(ColsToFix) To UBound(ColsToFix) .Cells(1, ColsToFix(iCol)).EntireColumn.TextToColumns _ DataType:=xlDelimited, _ FieldInfo:=Array(1, TypeOfCols(iCol)) Next iCol End With -- Jim |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does the date toggle between formats
Hi Ron,
My regional settings are set to d/m/y format. I deduced from OssieMac's reply that the dates in dd.mm.yy format are text generated by the accounting system as you suggest. However, a formula that uses the dates needs the macro to convert 12.09.07 to 12/09/07 otherwise it remains as text and the formula returns 00/01/1900. When the accounting system generated the dates as per the regional default, for some inexplicable reason rows 490 to 495 (for example) out of 1800 rows changed to US date format. Thus began my quest for a workaround. -- Jim "Ron Rosenfeld" wrote: On Mon, 29 Oct 2007 17:31:00 -0700, Jim G wrote: I have data that comes from an accounting/job costing system that generates Excel files. Unfortunately, the data can randomly change type a few hundred lines into the data. I changed the the date format in the accounting software to dd.mm.yy to avoid ambiguity but some dates still change to MDY for those that can resolve while others don't. For example, 12 Sept will change to 9 Dec while 24 Sept stays the same. Some of the numerical columns will change to text. I cannot be sure, but I believe your problem is likely due to the formatting of the accounting program as it gets interpreted by Excel. You write that the date format in your accounting program is dd.mm.yy. For me, that would mean that 12 Sep 2007 would be coming out of your accounting program as 12.09.07 -- not as 12 Sept That being the case, Excel will interpret your date in accord with the short date format of your Windows Regional Settings. (Control Panel/Regional Settings, or something similar) If your Windows Regional settings are set to US, for example: 12.09.07 -- 9 Dec 2007 24.09.07 is not interpretable by Excel, so it remains as a text string. But the Data/Text to Columns wizard, expecting a date in the format of dd.mm.yy IS able to interpret that text string, and does so as 24 Sep 2007. What you need to do, I believe, is to change the accounting date format generator to match the format of your Windows Regional Settings. (Or vice versa). --ron |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does the date toggle between formats
On Mon, 29 Oct 2007 22:01:00 -0700, Jim G
wrote: Hi Ron, My regional settings are set to d/m/y format. I deduced from OssieMac's reply that the dates in dd.mm.yy format are text generated by the accounting system as you suggest. However, a formula that uses the dates needs the macro to convert 12.09.07 to 12/09/07 otherwise it remains as text and the formula returns 00/01/1900. When the accounting system generated the dates as per the regional default, for some inexplicable reason rows 490 to 495 (for example) out of 1800 rows changed to US date format. Thus began my quest for a workaround. -- Jim Jim, OK, two points. Just to make sure we're talking about the same thing, when I write "regional settings", I am talking about the settings one sees going through Control Panel, and not settings within Excel. I still suspect some discrepancy in formatting, with Excel seeing certain output from Accounting as Text, and other output as true dates. Second, instead of using the Text-to-columns wizard to do the conversion, why not try doing the conversion differently and directly. Assuming ALL of the data comes out of the accounting package as dd.mm.yy, perhaps something like this would work better: ================================ Option Explicit Sub DateConvert() Dim rData As Range Dim c As Range 'set rData to include all the cells with date info 'or you could test that certain patterns exist Set rData = [A1:A100] For Each c In rData c.NumberFormat = "dd.mm.yy" If c.Text Like "##.##.##" Then c.Value = DateSerial(Right(c.Text, 2) + 2000, _ Mid(c.Text, 4, 2), Left(c.Text, 2)) End If Next c End Sub ============================== The c.NumberFormat line is to take care of an issue where the date might have been coerced by Excel into something else. --ron |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does the date toggle between formats
On Tue, 30 Oct 2007 06:29:54 -0400, Ron Rosenfeld
wrote: On Mon, 29 Oct 2007 22:01:00 -0700, Jim G wrote: Hi Ron, My regional settings are set to d/m/y format. I deduced from OssieMac's reply that the dates in dd.mm.yy format are text generated by the accounting system as you suggest. However, a formula that uses the dates needs the macro to convert 12.09.07 to 12/09/07 otherwise it remains as text and the formula returns 00/01/1900. When the accounting system generated the dates as per the regional default, for some inexplicable reason rows 490 to 495 (for example) out of 1800 rows changed to US date format. Thus began my quest for a workaround. -- Jim Jim, OK, two points. Just to make sure we're talking about the same thing, when I write "regional settings", I am talking about the settings one sees going through Control Panel, and not settings within Excel. I still suspect some discrepancy in formatting, with Excel seeing certain output from Accounting as Text, and other output as true dates. Second, instead of using the Text-to-columns wizard to do the conversion, why not try doing the conversion differently and directly. Assuming ALL of the data comes out of the accounting package as dd.mm.yy, perhaps something like this would work better: ================================ Option Explicit Sub DateConvert() Dim rData As Range Dim c As Range 'set rData to include all the cells with date info 'or you could test that certain patterns exist Set rData = [A1:A100] For Each c In rData c.NumberFormat = "dd.mm.yy" If c.Text Like "##.##.##" Then c.Value = DateSerial(Right(c.Text, 2) + 2000, _ Mid(c.Text, 4, 2), Left(c.Text, 2)) End If Next c End Sub ============================== The c.NumberFormat line is to take care of an issue where the date might have been coerced by Excel into something else. --ron Just another note: If your dates could also be in the 20th century, you will need to modify the DATESERIAL function YEAR argument to something like: Right(c.Text, 2) + 1900 + IIf(Right(c.Text, 2) < 40, 100, 0) --ron |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does the date toggle between formats
Ron, thanks for the well considered response and suggestions.
Firstly, the regional dates are set in Windows control panel as DMY. The accounting software is Axapta V3 which has a considerable population of 'bugs'. For example, I can send a report directly to Excel with ledger transaction dates displaying as d/m/yyyy normally for several hundred rows. Then the format will be lost and Excel will display the remaining several hundred rows as their serial number. I modified the Job Project report to force the format dd.mm.yy to avoid any ambiguity for my formulas/macros when creating summaries from the report when it's sent to Excel. As you can see it's opened a new can or worms. It drives me nuts. I'm hoping an upgrade to V4 will fix some of these, but that's a way off yet. Secondly, I'd like to give your suggestion a go. I can reformat the system text dates to dd.mm.yyyy to save using the +2000 (although we have no data in the 20th Century). Does "Set rData = [A1:A100]" mean I would need a different macro for each column of dates (if I had more than one). Note that, the number of rows can vary from a few hundred to thousands. I'm still curious as to why the first run on the existing code works on the first application and toggles back and forth for subsequent applications when repeated manual conversions does not. However, I'm assuming I can add your code to the existing code (after removing data type 4) and all will be good. Cheers -- Jim "Ron Rosenfeld" wrote: On Mon, 29 Oct 2007 22:01:00 -0700, Jim G wrote: Hi Ron, My regional settings are set to d/m/y format. I deduced from OssieMac's reply that the dates in dd.mm.yy format are text generated by the accounting system as you suggest. However, a formula that uses the dates needs the macro to convert 12.09.07 to 12/09/07 otherwise it remains as text and the formula returns 00/01/1900. When the accounting system generated the dates as per the regional default, for some inexplicable reason rows 490 to 495 (for example) out of 1800 rows changed to US date format. Thus began my quest for a workaround. -- Jim Jim, OK, two points. Just to make sure we're talking about the same thing, when I write "regional settings", I am talking about the settings one sees going through Control Panel, and not settings within Excel. I still suspect some discrepancy in formatting, with Excel seeing certain output from Accounting as Text, and other output as true dates. Second, instead of using the Text-to-columns wizard to do the conversion, why not try doing the conversion differently and directly. Assuming ALL of the data comes out of the accounting package as dd.mm.yy, perhaps something like this would work better: ================================ Option Explicit Sub DateConvert() Dim rData As Range Dim c As Range 'set rData to include all the cells with date info 'or you could test that certain patterns exist Set rData = [A1:A100] For Each c In rData c.NumberFormat = "dd.mm.yy" If c.Text Like "##.##.##" Then c.Value = DateSerial(Right(c.Text, 2) + 2000, _ Mid(c.Text, 4, 2), Left(c.Text, 2)) End If Next c End Sub ============================== The c.NumberFormat line is to take care of an issue where the date might have been coerced by Excel into something else. --ron |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does the date toggle between formats
On Tue, 30 Oct 2007 17:39:02 -0700, Jim G
wrote: Ron, thanks for the well considered response and suggestions. Firstly, the regional dates are set in Windows control panel as DMY. The accounting software is Axapta V3 which has a considerable population of 'bugs'. For example, I can send a report directly to Excel with ledger transaction dates displaying as d/m/yyyy normally for several hundred rows. Then the format will be lost and Excel will display the remaining several hundred rows as their serial number. My guess is that Excel may be seeing the "dates that look like dates" as text, and the serial numbers are the "real dates". I modified the Job Project report to force the format dd.mm.yy to avoid any ambiguity for my formulas/macros when creating summaries from the report when it's sent to Excel. As you can see it's opened a new can or worms. It drives me nuts. I'm hoping an upgrade to V4 will fix some of these, but that's a way off yet. Secondly, I'd like to give your suggestion a go. I can reformat the system text dates to dd.mm.yyyy to save using the +2000 (although we have no data in the 20th Century). Does "Set rData = [A1:A100]" mean I would need a different macro for each column of dates (if I had more than one). Note that, the number of rows can vary from a few hundred to thousands. No, you would just change your range object to refer to the data columns. Since I did not know the layout of your data, I just used an example. Here are other valid examples, Set rData = Range("A1:D1000") or Set rData = Range("A1:A1000,C20:C25000,F1:F100") --ron |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does the date toggle between formats
Hi Ron,
I tried this without any luck. My dates are YYYY so I amended the code to accomodate this. The worksheet runs the normal macro on each update so this must have resulted on cell changes because it went into a loop. Interesting, but too much for me. It looks I'll have to make sure that the original macro is only run once (99.9% of the time). Othewise, I can run thrice to force the toogle back to dd/mm/yyyy. Cheers -- Jim "Ron Rosenfeld" wrote: On Mon, 29 Oct 2007 22:01:00 -0700, Jim G wrote: Hi Ron, My regional settings are set to d/m/y format. I deduced from OssieMac's reply that the dates in dd.mm.yy format are text generated by the accounting system as you suggest. However, a formula that uses the dates needs the macro to convert 12.09.07 to 12/09/07 otherwise it remains as text and the formula returns 00/01/1900. When the accounting system generated the dates as per the regional default, for some inexplicable reason rows 490 to 495 (for example) out of 1800 rows changed to US date format. Thus began my quest for a workaround. -- Jim Jim, OK, two points. Just to make sure we're talking about the same thing, when I write "regional settings", I am talking about the settings one sees going through Control Panel, and not settings within Excel. I still suspect some discrepancy in formatting, with Excel seeing certain output from Accounting as Text, and other output as true dates. Second, instead of using the Text-to-columns wizard to do the conversion, why not try doing the conversion differently and directly. Assuming ALL of the data comes out of the accounting package as dd.mm.yy, perhaps something like this would work better: ================================ Option Explicit Sub DateConvert() Dim rData As Range Dim c As Range 'set rData to include all the cells with date info 'or you could test that certain patterns exist Set rData = [A1:A100] For Each c In rData c.NumberFormat = "dd.mm.yy" If c.Text Like "##.##.##" Then c.Value = DateSerial(Right(c.Text, 2) + 2000, _ Mid(c.Text, 4, 2), Left(c.Text, 2)) End If Next c End Sub ============================== The c.NumberFormat line is to take care of an issue where the date might have been coerced by Excel into something else. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Formats | Excel Discussion (Misc queries) | |||
Dealing with date formats on expiration date | Excel Programming | |||
Date Formats | Excel Discussion (Misc queries) | |||
date formats | Excel Discussion (Misc queries) | |||
Date formats | Excel Discussion (Misc queries) |