Why does the date toggle between formats

[Jim G]

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

 

[OssieMac]

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]

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.

 

[Ron Rosenfeld]

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

 

[OssieMac]

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]

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),JobCard!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 G]

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.

 

[Ron Rosenfeld]

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,

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

 

[Ron Rosenfeld]

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,

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

 

[Jim G]

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

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,

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

 

[Ron Rosenfeld]

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("A11000")

or

Set rData = Range("A1:A1000,C20:C25000,F1:F100")



--ron

 

[Jim G]

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

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,

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