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