VBA, Date formatting issue

Question

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.

Make sure both columns are both formatted as dates and [actually contain dates](https://superuser.com/q/299437/52365). — GSerg, Oct 04 '18 at 20:57
What issue are you having? This seems like a formatting issue rather than a code issue? — nathanscain, Oct 04 '18 at 20:58
Look at the three values you've shown in column X. Click Formulas, Formula Auditing, Show Formulas. What do those three cells say now? — , Oct 04 '18 at 21:08
@Jeeped , `15/08/2013`, `12/09/2013`, `03/10/2013` , showing `dd/mm/yyyy` format . Which is correct. It should be this. But when I paste it over, its switched too : `15/08/2013` (correct), `09/12/2013` (incorrect), and `10/03/2013`(incorrect) — excelguy, Oct 04 '18 at 21:16
What do you mean *'showing dd/mm/yyyy format'* ? If they aren't showing 41501, 41529 and 41550 then they were never dates to begin with, just text that looks like dates. — , Oct 04 '18 at 21:22
@Jeeped. They are showing, 41501, 41529, 41550. I thought you meant inside the cells. — excelguy, Oct 04 '18 at 21:27
You say, in your question. ***"As you can see it goes from mm/dd/yyyy to dd/mm/yyyy,"***. THEN you say ***"How can I format my paste to go to dd/mm/yyyy."*** So... what are you wanting that isn't already happening. They are in the format you want, no? If not, highlight whatever column you want to change and change the format. Done. Under the hood these are dates. They are stored as a 5 digit number. The only difference here is how excel is showing them to you. It's merely a formatting issue, which is done by changing the cell format. — JNevill, Oct 09 '18 at 13:17
@JNevill Seems like the dates are switching ordering of months and days. See update. But if you are saying this is a non issue and won't affect my data on a graph I make from these days, then it should be okay — excelguy, Oct 09 '18 at 13:29
See https://stackoverflow.com/questions/20375233/excel-vba-convert-text-to-date and also https://stackoverflow.com/questions/43095992/converting-date-in-excel and also https://stackoverflow.com/questions/51494768/convert-a-complete-column-number-format-into-date-dd-mm-yyyy-in-excel-vba-not — TylerH, Oct 09 '18 at 13:42
Given the underlying numeric values of the dates are changing (e.g. from 41529 to 41617), it would seem the issue is some form of date translation in the process of collecting the data to be copied, and then how it is copied into the cell. Can you expand your code sample to include that (e.g. definition of PasteArr, how you populate it etc)? — TechnoDabbler, Oct 10 '18 at 01:37
@Techno Dabbler, yes ill include the details. Please see updated question. — excelguy, Oct 10 '18 at 02:14
You don't show the variable declaration of your arrays. If they're variants, the copies work. However if your arrays are defined as strings, you get the exact issue you've described, where the days/months are transposed on ambiguous dates. — TechnoDabbler, Oct 10 '18 at 02:26
Have you put a breakpoint on your output line and used the immediate/local/watch window to examine the contents of your array? Getting one correct result and two incorrect results is suspicious, it may not be a display/format issue. — ProfoundlyOblivious, Oct 10 '18 at 02:44
What variable types are your arrays declared as and what country are you (or the end user) in? — Absinthe, Oct 10 '18 at 18:40
Lastrow is long, PasteArr is variant. No strings. End User is in Canada — excelguy, Oct 10 '18 at 18:57
Check this out: https://stackoverflow.com/questions/36043415/transposing-array-with-dates-changes-dates so possible store it as a string and convert to a date once transposed?... — Sorceri, Oct 10 '18 at 19:35
Why not copy the integer values and apply the correct format afterwards? — Rik Sportel, Oct 12 '18 at 11:37

score 2 · Answer 1 · answered Oct 09 '18 at 13:31

2

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

answered Oct 09 '18 at 13:31

user11909

1,235
11
27

score 1 · Answer 2 · answered Oct 09 '18 at 13:27

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)

answered Oct 09 '18 at 13:27

Dominique

16,450
15
56
112

its odd , ill do a custom format for dd/mm/yyyy , but when I go to see the number formatting again its just at the date section. Is this normal? – excelguy Oct 09 '18 at 13:28
Maybe your sheet is protected in some way. Is it yours or did you get it from somebody? – Dominique Oct 09 '18 at 13:29
got it from someone. See my updated question for some more details. – excelguy Oct 09 '18 at 13:31
I think the problem could not be simply brushed aside by saying format issue. In my workplace we are living with similar type of problem for around last 12/13 years. However we worked around with some silly crude workaround approach suitable only in context to actual real time data we process, but I must acknowledge @Jeeped answer to post "Conflict between innertext VBA and excel" and "Excel VBA - Convert Text to Date"? Are also a great workaround. But the fundamental problem is still very much intact. .I am posting a new question giving the actual problem we faced – Ahmed AU Oct 11 '18 at 07:27

TechnoDabbler · Answer 3 · 2018-10-10T02:39:17.813

1

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?

edited Oct 10 '18 at 02:39

answered Oct 10 '18 at 02:33

TechnoDabbler

1,245
1
6
12

I don't declare any of my variables as strings though. They are all variant, long or boolean. My pastearr variable is variant. – excelguy Oct 10 '18 at 11:33
@excelguy Variant isn't a datatype though, it just means the array can hold any datatype (and the compiler will do its best to find the correct datatype if the source is ambiguous). So it doesn't matter if the array is variant - if the content it holds is cast to `String`, you will get the trouble mentioned here. – Vegard Oct 13 '18 at 15:34
@Vegard ... exactly. Even with an array declared as a Variant ... assign a date to it and you can see that the underlying type is Date ... and it through (for example) Application.Transpose and you'll see that the underlying type is changed to String. – TechnoDabbler Oct 15 '18 at 02:08

score 1 · Answer 4 · answered Oct 10 '18 at 16:17

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!

score 1 · Answer 5 · answered Oct 16 '18 at 13:38

The trick is to assign values from Column X to column Y using then Range.value property. This will ensure that the data is transferred in the same format it exist in X column (whether date, number, string... etc). If you set the same display on two columns then you will see same thing on both columns.

I find your code convoluted so I have rewritten the logic to search first column 'X' and put unique occurrences on second column 'Y'

Public Sub findOrAdd()
    Const COLUMN_SOURCE = "B"
    Const COLUMN_DEST = "D"
    Const ROW_STARTDATA = 2

    Dim x As Long, y As Long
    Dim foundMatch As Boolean

    Dim sht As Worksheet
    Set sht = Sheet1

    x = ROW_STARTDATA
    Do Until sht.Range(COLUMN_SOURCE & x).Value = "" 'X -variable loop walks through all cells in source column
        Debug.Print "Doing row " & x & " =" & sht.Range(COLUMN_SOURCE & x).Value
        foundMatch = False
        'search for value of current cell in destcells
        y = ROW_STARTDATA
        Do Until sht.Range(COLUMN_DEST & y).Value = "" 'Y -variable loop walks through all cells in dest column - checking if it exists
            If sht.Range(COLUMN_SOURCE & x).Value = sht.Range(COLUMN_DEST & y).Value Then
                'match found stop searching and do nothing
                foundMatch = True
                Exit Do
            End If
            y = y + 1
        Loop

        If foundMatch = False Then
            'Y loop completed and match was not found.
            'Append content as end of destination cells
            sht.Range(COLUMN_DEST & y).Value = sht.Range(COLUMN_SOURCE & x).Value

            '** NOTE value is added by assigned cell.value, which is not pasting.
            '** If the formats of the source and destination address are done the same then they will display the same thing in excel
        End If
        x = x + 1
    Loop
End Sub

Note: Blank rows will cause loops to exit

VBA, Date formatting issue

5 Answers5

Linked