Array to Table, Date Format Error

GlennL

New Member
Joined
Sep 25, 2018
Messages
23
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hello,
I am moving the data from an array to a table using this

VBA Code:
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?
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,411
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Sep 25, 2018
Messages
23
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
VBA Code:
CDate(Format(DateAdd("m", 3, DateAdd("d", 1, ServiceDate)), "dd/mm/yyyy"))
The array element still is 4/06/2021
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,980
Office Version
  1. 365
Platform
  1. Windows
How are you loading the array?
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,411
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

How are you loading the array?
Good question. That is probably the beginning of the problem
 

GlennL

New Member
Joined
Sep 25, 2018
Messages
23
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

VBA 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).

VBA Code:
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
 
Solution

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,411
Office Version
  1. 2016
Platform
  1. Windows
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 :)
 

Forum statistics

Threads
1,136,445
Messages
5,675,899
Members
419,591
Latest member
mersanko

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top