Day & Month Swapping around when day is less than 13

[RMB]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have taken over a spreadsheet and macro that a previous colleague built for a regular report that I am required to run for my team. The report works great with one exception – certain dates appear to be impacted when the day is 1 to 12 (inclusive), in that the days and month swap around. Up until now, I have been manually amending this each week, but as the data grows, this is becoming more and more time consuming.<o></o>
<o></o>
Basics of what I am doing:<o></o>
· I extract data via a system that exports it in a CSV file – dates are in the format of DD/MM/YY<o></o>
· I paste the data into my s/sheet and then run the below macro over it – this merges the columns and puts everything into its own columns so that it can be easily pivoted from<o></o>
· There are several columns that are dates – any date that is from day 13 or above i.e. 13/06/11 remains correct (DD/MM/YY), however, if the date is 03/06/11 once the macro is run, it formats the date as 6/03/2011. So, it not only sways the day and month around, but also changes the year to being 4 digits.<o></o>
<o></o>
Public Sub mergeColumns()<o></o>
<o></o>
Dim paste_point As Range<o></o>
<o></o>
Dim keep_row As Integer<o></o>
Dim merge_row As Integer<o></o>
Dim keep_columns As Integer<o></o>
Dim merge_columns As Integer<o></o>
<o></o>
Dim keep_array As Variant<o></o>
Dim merge_array As Variant<o></o>
Dim merge_column_array As Variant<o></o>
<o></o>
' Arrays for storing all the data we wish to copy<o></o>
keep_array = Range(Range("$F$1").Value).Value<o></o>
merge_array = Range(Range("$F$2").Value).Value<o></o>
merge_column_array = ArrayFunctions.ArrayTranspose(ArrayFunctions.SubArray(merge_array, 1, UBound(merge_array, 2), 1, 1))<o></o>
<o></o>
keep_columns = UBound(keep_array, 2)<o></o>
merge_columns = UBound(merge_column_array, 1)<o></o>
<o></o>
Set paste_point = Range(Range("$F$3").Value)<o></o>
<o></o>
Application.ScreenUpdating = False<o></o>
Application.DisplayAlerts = False<o></o>
Application.StatusBar = False<o></o>
Application.Calculation = xlCalculationManual<o></o>
<o></o>
For keep_row = 1 To UBound(keep_array, 1)<o></o>
<o></o>
merge_row = merge_columns * (keep_row - 1)<o></o>
<o></o>
paste_point.Offset(merge_row, 0).Resize(merge_columns, keep_columns).Value = Application.WorksheetFunction.Index(keep_array, keep_row, 0)<o></o>
<o></o>
paste_point.Offset(merge_row, keep_columns).Resize(merge_columns, 1).Value = merge_column_array<o></o>
<o></o>
paste_point.Offset(merge_row, keep_columns + 1).Resize(merge_columns, 1).Value = ArrayFunctions.ArrayTranspose(ArrayFunctions.SubArray(merge_array, 1, UBound(merge_array, 2), keep_row + 1, keep_row + 1))<o></o>
<o></o>
Next keep_row<o></o>
<o></o>
Application.ScreenUpdating = True<o></o>
Application.DisplayAlerts = True<o></o>
Application.StatusBar = True<o></o>
Application.Calculation = xlCalculationAutomatic<o></o>
<o></o>
End Sub<o></o>
<o></o>
I am not that great with macros and would sincerely appreciate any help! : ) <o></o>

 

[p45cal]

re:"I paste the data into my s/sheet"

If you were to use the text import wizard (you might have to change the name of the file to be imported by changing its extension to .txt from .csv) you get the opportunity to set how the dates are interpreted when imported.

What version of Excel?

 

[RMB]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Thank you for such a quick reply!<o></o>
<o> </o>
I am using Excel 2007. I have just saved the export data as a text file and the format of all of the dates is as follows:<o></o>
<o> </o>
22/02/2011 1:37:56 PM<o></o>
<o> </o>
I paste this raw data into one sheet and then format it in another to drop the time stamp with “=TEXT('Opps Raw Data'!D4,"DD/MM/YY")” <o></o>
<o> </o>
The macro is then run over the text version of the date (in the above example would be 22/02/11) – if the day is great than or equal to 13 the date stays as is, however if the day is below 13 it swaps the day and month around and changes the year to four digits in length.<o></o>
<o> </o>
I apologise if I am not making much sense. I am by no means an expert on using excel or macros!<o></o>

Regards,
Mandy

 

[p45cal]

re:"I paste the data into my s/sheet" and "I paste this raw data into one sheet"

It is at this very early stage that things can go wrong. Excel has already tried to be helpful - but fails: It has already tried to interpret text that looks like a date into a date. You can see which cells it has managed to do this with in two ways:
1. by widening the column that you've pasted the date data into, you'll see some data aligned to the left, and some to the right. That data which is aligned to the right has been translated into real excel dates.
2. If you select that column and reformat the cells to General, you'll see a mix of numbers and text; the numbers are excel dates, the remaining cells still showing text are just that.

Now this difference is obvious and you can work around it, but (depending on your computer's locale setting, US or UK say) THIS MAY NOT BE YOUR MAIN PROBLEM, which is pasted dates such as
03/06/2011 1:33:02 PM
may already have been interpreted wrongly by this stage, where the date was meant to be 3rd June, it gets interpreted as the 6th March. If your computer is set to UK (day/month/year) and you're importing UK dates this may not be a problem. Where are you and what is the computer's locale setting (dd/mm/yy or mm/dd/yy ?)

This second problem is much more difficult to work round, because the errors are not obvious.

You may have wised up to this and tried to circumvent it by formatting the destination cells as Text before pasting the data, but the same problem arises when you apply your =TEXT(~.. formula - Excel tries to be helpful; but interprets stuff wrongly in just the same way. If your dates text comes in a consistent way with leading zeroes in the months and day parts then you could more reliably convert to real dates and lose the timestamp with a formula such as:
=DATE(MID(A12,7,4),MID(A12,4,2),LEFT(A12,2))
This will only work reliably if you format the cells destined to receive dates as Text before you paste data into them.

Which is why I suggest using the import text wizard rather than just pasting the data in since it handles all this stuff correctly and you can control it..

I'm currently experimenting on Excel 2003, but will get to a machine with Excel 2010 later today. I think the behaviour is the same, but will check.

 

[RMB]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Thank you again for your reply!<o></o>
<o> </o>
I think I have worked out how to fix the issue. I have used “=DATEVALUE(TEXT('Opps Raw Data'!D4,"DD/MM/YY"))” this has dropped the time stamp, I then paste values the data into text formatted cells, when the macro is run the output is to cells that are formatted as a date *14/03/01 – this appears to work! I have not checked yet what, if any, impact that this might have on my pivot tables – but will soon find out when I run my next report.<o></o>
<o> </o>
Just FYI – I am in Australia and have the settings set as dd/mm/yy.<o></o>
<o> </o>
I sincerely appreciate your assistance, I tried and looked at the things that you suggested which got me thinking and lead me to play around a little further. I am very grateful that come Monday, I will not have to be sitting at my desk manually fixing up hundreds of incorrect dates! <o></o>
<o> </o>
Kindest regards,<o></o>
Mandy<o></o>

 

[Ruddles]

I always format dates as "dd-mmm-yyyy" when I'm playing around with them as Excel is less likely to get its knickers in a twist when you spell the month name out in full.

 

[RoryA]

I don't really see the point of:
=DATEVALUE(TEXT('Opps Raw Data'!D4,"DD/MM/YY"))

since you are converting a date to text and then back to a date?

 

[RMB]

Hi Rory,
I only have a basic skill with excel. I have used this formula so that I can drop the time stamp. Perhaps there is a much more effecient way of doing this, but for now this gets the job done : )
Regards,
Mandy

 

[Ajalon]

Hi. sorry I'm late into this thread but have only just joined the party so this is my first post.

I agree with Ruddles

I always format dates as "dd-mmm-yyyy" when I'm playing around with them as Excel is less likely to get its knickers in a twist when you spell the month name out in full.

I had a similar problem with VBA, but if you can get your date information into and alphanumeric format (dd-mmm-yyyy) BEFORE you Copy, the problem will disappear.

Regards

Allan