Date formatting issue sheet to VBA Array to Sheet

[SimonHow]

I have a number of sheets which I am combining to one sheet using a VBA procedure which collects the data from source sheets, places into a dynamic array (so that can be re dimensioned to deal with different data set sizes) there are a couple of calculations within the array, before passing and appending to a master sheet.

I collect the data using 8 instances of the line of code whether i is telling me which line of data to collect
I pass the data using the second line of code, where the range to pass is defined by the UBound dimension calculated earlier and the next empty row found (NR)

TArray(3, n) = .Range("E" & i).Value ' Date

NR = .Range("A1048576").End(xlUp).Row + 1
                .Range("A" & NR & ":I" & NR + n).Value = Application.Transpose(TArray)

Everything works perfectly, except an issue that has crept in with date formats, which I only found when the resulting pivot table was sorting incorrectly.

in cell E28 on my source sheet I have the date: 21/03/2022 (in other words 21 March 2022 UK Style)
in cell E29 on my source sheet I have the date: 08/03/2022 (in other words 8 March 2022 UK Style)

the excel format type for column E is date

to the master sheet it passes:

to E28: 21/03/2022 (21 March 2022 - UK Form)
to E29: 03/08/2022 (3 August 2023 UK Form)

any ideas how I can fix this issue

Many thanks advance

 

[Jeffrey Mahoney]

 

[MARK858]

Try wrapping it in CDate or if that doesn't work CLng

TArray(3, n) = CDate(.Range("E" & i).Value)

or

TArray(3, n) = CLng(.Range("E" & i).Value)