Date Format Erratic on Transfer From Array To Range

[Default300]

Hi. I've run into another problem! Baffling one this time!!

I have an Array.
I think it is an Array contained within a Variant Variable, rather than an Array of Variant DataType.
It has 2 Dimensions.

I want to output / paste the entire contents onto a Worksheet Range.
The Range in question has identical dimensions, in the correct orientation.
The Format of all Cells in this Range is initially General and not deliberately changed by any VBA code (but see below).

What works:
Each "Column" or "Field" of Data in the Array is transferred to the correct Column Range on the Worksheet.

Problem:
One of the Array "Fields" holds Date Data.
When transferred to the Worksheet, results are inconsistent...

Each Array Element displays either syndrome (a) or (b), when transferred to the Worksheet, depending on the value of the Date (dd) Element when still in the Array.

Dates 01 - 12 (inclusive): appearance and values are as per group (a).
Dates 13 - 31 (inclusive): appearance and values are as per group (b).

Group (a)
Alignment Appearance: Right
Alignment Setting (in Excel UI's Cell Format dialog): General
Number Format: Date (*14/03/2001)
Values: changed (month swapped with date)

Group (a) seems to have been automatically reformatted, presumably by Excel. I'm fine with the Alignment and Number Format (both correct for a date, I believe). The worrying thing is that the value has changed, making it unuseable. The formulae (MONTH and DAY) return the opposite of what is required.

Example
Array Value = 01/02/2010
Cell (A1) Value = 02/01/2010

Cell (B1) Formula = DAY(A1)
Cell (B1) Value = 02

Cell (C1) Formula = MONTH(A1)
Cell (C1) Value = 01

Group (b)
Alignment Appearance: Left
Alignment Setting (in Excel UI's Cell Format dialog): General
Number Format: General
Values: correct (date to left of month as it should be).
Values useable (formulae return correct month and date, from Worksheet cell, which is also correct relative to array value).

Group (b) does NOT seem to have been automatically reformatted by Excel. It's great that it is useable, but slightly worrting that it does not appear to have been recognised as a date.

Example
Array Value = 01/02/2010
Cell (A1) Value = 01/01/2010

Cell (B1) Formula = DAY(A1)
Cell (B1) Value = 01

Cell (C1) Formula = MONTH(A1)
Cell (C1) Value = 02

Number Format and Alignment Setting were checked with Excel UI's Cell Format dialog.

Values were checked using formulae [ =MONTH(A1) ], and [ =DAY(A1) ]. So it is definitely not just the format that has changed.

In VBA, I checked each Element of that "Field" as follows.

[FONT=Courier New]Dim lngLoop1 As Long

For lngLoop1 = LBound(varContactRecordsTransposed, 1) To _
               UBound(varContactRecordsTransposed, 1)

Debug.Print varContactRecordsTransposed(lngLoop1, 4); Tab(14); _
            IsDate(varContactRecordsTransposed(lngLoop1, 4)); Tab(22); _
            TypeName(varContactRecordsTransposed(lngLoop1, 4))

Next lngLoop1[/FONT]

Dates and Months are displayed in correct order in the Immediate Window.
IsDate returns True for each Element.
TypeName returns String as the DataType for each Element. (I don't think this should be a problem, though I'm not sure why it is a String. The Format of the Cell which was originally read into the Original Array was: Date (*14/03/2001).

The Current Array is a Transposed version of the Original Array, created using this code:

[FONT=Courier New]Dim varContactRecordsTransposed As Variant
Let varContactRecordsTransposed = Application.WorksheetFunction.Transpose(varContactRecords())[/FONT]

The Orginal Array was Dimensioned as follows:

[FONT=Courier New]Dim varContactRecords()
ReDim varContactRecords(1 To 5, 1 To lngSumOfCellsContainingContacts)[/FONT]

The Original Array copied Values from the Worksheet using this Code:

[FONT=Courier New]Case 4              'ContactDateDDMMYYY

    Let varContactRecords(bytLoop2, lngNumberOfContactRecordsFound) = _
        Intersect(.EntireColumn, _
        Worksheets(strNameOfWorksheetDataEntry) _
        .Range("namSheetDataEntryContactsMainSupraHeaderRowDateFullNonMerged"))[COLOR=Red][B].Value[/B][/COLOR][/FONT]

I also tried this with the same result (though I'd love to know which is more correct):

[FONT=Courier New]Case 4              'ContactDateDDMMYYY
  
    Let varContactRecords(bytLoop2, lngNumberOfContactRecordsFound) = _
        Intersect(.EntireColumn, _
        Worksheets(strNameOfWorksheetDataEntry) _
         .Range("namSheetDataEntryContactsMainSupraHeaderRowDateFullNonMerged"))[/FONT]

The Current Array copies Values to the Worksheet using this Code.
NB: The entire 5 "Field" Array is transferred as one, to a 5 Column range (NOT Column by Column):

[FONT=Courier New]Set rngDestinationRange = _
    rngOffsetAnchor _
    .Offset(lngOffsetRows, lngOffsetCols).Resize( _
    UBound(varContactRecordsTransposed, 1) - LBound(varContactRecordsTransposed, 1) + 1, _
    UBound(varContactRecordsTransposed, 2) - LBound(varContactRecordsTransposed, 2) + 1)


[B]Let rngDestinationRange = varContactRecordsTransposed[/B][/FONT]

Incidentally the Locale I'm using (in Control Panel's Regional & Language Settings) uses Short Date: 20/06/2010 and Long Date: 20 June 2010.

Excel's Option Setting for "Extend List Formats and Formulas" is True / On. The cell above the column in question (the header) is also formatted as General and contains text.


Workaround:
Before Copying the Values to the Worksheet, Format Cells as Text.
After Copying Format Cells as Date (*14/03/2001).

I can do this if necessary, but am concerned that the results I'm seeing are symptomatic of an underlying error on my part. I don't want to mask a problem with symptomatic relief if I can cure it completely and avoid it in the future.

Clue:
My deduction is that the fact that a year has 12 months must have something to do with Excel's decision to treat dates for 01-12 differently to those from 13-31.

 

[AtholH]

The FormulaLocal method is superior in that it also formats the dates correctly for 3rd party programs interfacing with Excel.

The previously mentioned method of updating the range format after the array copy works okay solely within Excel in that it displays the date in the correct format but it does not work if one needs to import the Excel data into another program like Access. Even though the date is formatted correctly and shows as a date format within Excel some dates are seen as Text by third-party programs. This is especially the case in non-US date formats.

 

[mjnaylor]

Very useful thread.

I overcame the problem by ensuring that the array used is a string type and then no conversion took place when setting the Excel range to the array values. This does mean in some cases having an additional function to create an output array of type string from whatever array you might have as an containing a variety of different types. I ensured that the output array was string by using CSTR to populate the values.
The clue was that dates with dd between 1-12 were being converted to mm/dd/yy dates in Excel format (i.e. a number) while dates with dd between 13-31 were not converted and when from array to range as text.

I realise the thread is quite old, but I had this problem today and found the Range.FormulaLocal property solved it. On my UK set-up machine:

Dim v

Redim v(0 to 1, 0 to 1)

v(0,0) = "01/06/2010"

Sheet1.Cells(1,1) = v
Sheet1.Cells(1,1).Formula = v
'' result in Cell(1,1) sheet values of 06/01/2010

Sheet1.Cells(1,1).FormulaLocal = v
Sheet1.Cells(1,1).FormulaR1C1Local = v
'' both result in 01/06/2010 as required.

All the XXXXXXLocal properties* seem to do something similar: i.e. work on the basis of the machines/users locale settings. (I gather there can be drawbacks where formulas and multiple locales (e.g. different language versions of excel) are involved, but time currently too tight for the research I'm afraid. Simple copying of data from array variants to ranges it so far seems a fairly robust and pain free solution (compared with those above).

* I'm not sure which version of excel first had these, but they certainly seem to be available in the versions mentioned above; perhaps they always have been?

Anyway, hope this helps a subsequent visitor some day.

bea