Array to Table, Date Format Error

[GlennL]

Hello,
I am moving the data from an array to a table using this

tbl.DataBodyRange = Application.Transpose(arrTemp)

The array dimensions have mixture of variable of sting, long, and date.
Where there are dates in the elements, the date-variables have the format of either d/mm/yyyy or dd/mm/yyyy.

If the array date is pushed into the table where the format is d/mm/yyyy the day becomes the month and the month becomes the day. eg array 4/06/2021 -> table 6/04/2021.
This is not a format issue, the array is 4th June 2021 and the table is 6th April 2021.

I have checked the cells/column date format and the date format is set as dd/mm/yyyy.

Ideas of how to fix?

 

[Zot]

The VBA will treat date as US format as it reads from sheet, ignoring the format. So, those using British format has more to deal with.

I think the safe way is to either convert to string or date value before transfer and reformat those date again.

 

[GlennL]

Preceding the VBA code, I found the leading zero of the day was stripped in a power query. I changed the Date Locale to English UK and the zeros remain in the day.

I thought I had the fix, then however, the problem is still in the the Array.

The date imports correct into the array from the power query table.

I then do a Date.Add as per this solution VBA DateAdd Anomaly. It is the Date.Add which omits the leading zero of the day.

Hoping to get the correct date format I tried this

CDate(Format(DateAdd("m", 3, DateAdd("d", 1, ServiceDate)), "dd/mm/yyyy"))

The array element still is 4/06/2021

 

[Fluff]

How are you loading the array?

 

[Zot]

How are you loading the array?

Good question. That is probably the beginning of the problem

 

[GlennL]

How are you loading the array?

1. I put data into 2 excel tables. This data comes from 2 SQL tables via power query.
2. Both tables are put into arrays (array1 & array2) via array=application.transpose(array)
3. data is loaded into array3 by looping through array1 and array2
4. array3 dates calculated using DateAdd.

This is a basic synopsis of the code

Sub ArrDateBroke()

Arr = Application.Transpose(Range("tbl").ListObject.DataBodyRange)

Arr(2, 1) = DateAdd("m", 1, Arr(1, 1))

Range("tbl").ListObject.DataBodyRange = Application.Transpose(Arr)

End Sub

When using the above code, you can see when stepping through the code, Arr(1,1) brings the date in as a serial and then in Arr(2,1) the date is in the date format of 4/6/2021 (d/mm/yyyy). When the array is transferred to the sheet, it changes to 6/4/2021 (m/d/yyyy).

I just tried this code and it transferred to the sheet correct. The difference now being Arr(2,1) is in serial date format, not (d/mm/yyyy).

Sub ArrDateFixed()

Arr = Application.Transpose(Range("tbl").ListObject.DataBodyRange)

Arr(2, 1) = CLng(DateAdd("m", 1, Arr(1, 1)))

Range("tbl").ListObject.DataBodyRange = Application.Transpose(Arr)

End Sub

 

[Zot]

So, does this mean the problem you are facing is solved?

The Arr(1, 1) is read as m/d/yyyy. and the DateAdd will perform just fine. For me, keeping the date in serial in VBA and only then convert to the format you prefer at the end will avoid headache