VB: Macro converting to American dates!

[imperium1980]

Hi all,
I have put a macro together that pulls data from a sheet which has a date column formatted in standard UK dd/mm/yyyy. Whenever I run the macro it pulls my data together and dumps it into a new sheet called 'Summary' Everything is working fine except VB changes my dates to American formats, i.e 02/01/2012 (2nd Jan) becomes 01/02/2012 (1st Feb). This is driving me nuts! Any ideas how I get around this?

Option Explicit
DefInt I-M
DefStr S
Sub Summary()
Dim i, j, k, l, m, iDays
Dim sSummary(), sDate, sMC, sStyle, sPack
Select Case ActiveSheet.Name
Case "January", "March", "May", "July", "August", "October", "December"
iDays = 31
Case "February"
iDays = 29
Case Else
iDays = 30
End Select
l = 0
Range("E7").Select
For i = 0 To iDays - 1
sDate = ActiveCell.Offset(-5, i)
sMC = ActiveCell.Offset(-4, i)
sStyle = ActiveCell.Offset(-3, i)
sPack = ActiveCell.Offset(-2, i)
For j = 0 To 26
If ActiveCell.Offset(j, i) <> "" Then
ReDim Preserve sSummary(7, l)
sSummary(0, l) = sDate
sSummary(1, l) = sMC
sSummary(2, l) = sStyle
sSummary(3, l) = sPack
For m = 4 To 6
'MsgBox ActiveCell.Offset(j, m - 7)
sSummary(m, l) = ActiveCell.Offset(j, m - 7)
Next
sSummary(7, l) = ActiveCell.Offset(j, i)
l = l + 1
End If
Next
Next
Sheets("New Summary").Select
Range("A2").Select
Do
If ActiveCell <> "" Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell = ""
For j = 0 To l - 1
For i = 0 To 7
ActiveCell.Offset(j, i) = sSummary(i, j)
Next
Next
End Sub

 

[jproffer]

Try forcing it to format the way you want.

Format([date or range or whatever], "dd/mm/yyyy")

BTW, most februarys have 28 days ...except leap year of course.

 

[imperium1980]

Thanks for the quick response, I tried the forced formatting earlier and it still screws the dates up.

I've already set a reminder in my Outlook to fix the macro for February next year. Providing I get it working that is!

 

[Norie]

Have you tried just changing the format of the column?

The dates might only be displayed as US dates but the values in the cells are actually 'real' dates.

 

[Marcelo Branco]

Maybe

sSummary(0, l) = Format(sDate, "mm/dd/yyyy")

M.

 

[Leith Ross]

Hello imperium1980,

You can change the date formats using the Text to Columns... in the Data menu.

• Select the column of dates and choose Data > Text To Columns. This opens the Convert Text To Columns Wizard .
• In Step 1 select either option as you only have one column of data selected anyway.
• Click Next twice.
• Now in Step Three of the wizard, select the Date option and select the formatting for the displayed data. So if the data has been typed in DMY format, select DMY. If it has been typed in MDY format, choose MDY. Click Finish.

 

[Biz]

Try Custom function below

Function ConvertUsDates(aCell)
If IsDate(aCell) Then
    ' Reverse month and day
    ConvertUsDates = DateSerial(Year(aCell), Day(aCell), Month(aCell))
Else
    ConvertUsDates = aCell
End If
End Function

Does it help?

Biz

 

[Norie]

The only think that might be causing the problem is that the array is declared as string.

Try removing this.

DefStr S

Or using DateValue, for example.

sSummary(0, l) = DateValue(sDate)

 

[imperium1980]

The only think that might be causing the problem is that the array is declared as string.

Try removing this.

DefStr S

Or using DateValue, for example.

sSummary(0, l) = DateValue(sDate)

Tried the easiest suggestion first and it worked! Thanks to all for offering their wisdom, much appreciated.
Neil