VBA, Date formatting issue

I have a code that

1) compares dates from Col X to Col Y.

2)paste dates to col Y if there is no match between columns.

Column X my format looks like

08/15/2013 09/12/2013 10/03/2013 

But when it pastes to column Y it goes,

15/08/2013 12/09/2013 03/10/2013 

How can I format my paste to go to dd/mm/yyyy.

Added more code to show array:

   ReDim PasteArr(1 To 1, 1 To 6)     subcount = 1      For Cell1 = 1 To UBound(DataArr(), 1)         For Each Cell2 In BusDates()             If DataArr(Cell1, 1) Like Cell2 Then                 Matched = True                 Exit For                                      'if it matches it will exit             ElseIf Cell2 Like BusDates(UBound(BusDates), 1) Then 'if it gets to the end, it's truly unique and needs to be added                  For index = 1 To 6                     PasteArr(subcount, index) = DataArr(Cell1, index)                 Next index                  subcount = subcount + 1                  PasteArr = Application.Transpose(PasteArr)                 ReDim Preserve PasteArr(1 To 6, 1 To subcount)                 PasteArr = Application.Transpose(PasteArr)                  Matched = False              End If         Next Cell2          If Matched = False Then             BusDates = Application.Transpose(BusDates)             ReDim Preserve BusDates(1 To UBound(BusDates) + 1)             BusDates = Application.Transpose(BusDates)             BusDates(UBound(BusDates), 1) = DataArr(Cell1, 1)         End If      Next Cell1     Worksheets("stacks").Range("M" & LastRow + 1 & ":" & Cells(LastRow + UBound(PasteArr, 1) - 1, 18).Address).Value = PasteArr 

What i've tried: Changing the format of cells

15/08/2013 12/09/2013 03/10/2013 

which is now the correct format for column X.

But this is pasting into column Y as:

which is

15/08/2013 - correct 09/12/2013 - incorrect 10/03/2013 - incorrect. 

4 Answers

Answers 1

Check your cell format. It should be:

Number   Custom     dd/mm/yyyy (depending on your locale, in my case (Dutch) it's dd/mm/jjjj) 

Answers 2

Use dd/MM/yyyy as cell format. Lowercase m stands for minutes, uppercase M for months.

Answers 3

Per my comment above, given you're using arrays and not copying ranges/cells directly. If your arrays are declared as string arrays, you will get the issue of transposed days/months. For example:

Could that be the issue?

Answers 4

I live in Portugal and sometimes I have issues of the same nature regarding the date formatting options. Usually, what I do (and normally it works), is using and abusing of the DateSerial function. For instance, if I wanted to populate your PasteArr array I would do:

PasteArr(subcount, index) = DateSerial(Year(DataArr(Cell1, index)), Month(DataArr(Cell1, index)), Day(DataArr(Cell1, index))) 

To write a date on a cell I do the following:

Worksheets("stacks").cells("M" & LastRow + 1).formulaR1C1 = DateSerial(Year(PasteArr(subcount, index)), Month(PasteArr(subcount, index)), Day(PasteArr(subcount, index))) 

Honesty the previous procedure seems a little bit silly. Really, it does! However it solves the problem with the date formatting dd/mm/yyyy vs mm/dd/yyyy problem. If you ask me why, I don't know exactly how it works! But it works every time!

If You Enjoyed This, Take 5 Seconds To Share It

• Facebook
• Twitter
• Google+
• StumbleUpon
• Digg
• Delicious
• LinkedIn
• Reddit
• Technorati