My array is mixing up the format of my dates, and I can't work out why

TheRedCardinal

Board Regular
Joined
Jul 11, 2019
Messages
174
Hi all,

I've not come across this before so I'm a bit stumped.

The basics of my code is that it loads a range from a worksheet into an array, manipulates the data in that array and converts it into another array, and that outputs that second array to another worksheet.

All cells in the target worksheet are formatted as text, mainly to maintain leading zeroes in some of the columns.

2 of the columns contain dates. They are in the UK format in the source data (DD/MM/YYYY).

When my code runs, they are output in the US format (MM/DD/YYYY).

When I debugged the program, I noticed that the dates in each column are shown in the Locals window as surrounded by # symbols:

#03/08/2020#
#04/08/2020#

This is true in BOTH the source array and output array.

The output is switching around the DD and MM data - rather than just allocating the wrong fields.

Example:

Source data = 03/08/2020
In Locals in the arrays = #03/08/2020#
Output in Text on worksheet = 08/03/2020

All my localisations are UK based.

What is going on here?
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Forum statistics

Threads
1,136,206
Messages
5,674,404
Members
419,506
Latest member
mpazr001

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