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.

 

[Stephen_Tsui]

Default300:

I also tried the same problem before,
and use below method to solve (may be stupid)

Extract the three part value by using function Split(13/01/2010,"/") in vba,
and rearrange back to suit my computer's default date format, e.g. (2010/01/13),

Such as follow code:

Sub try()

Dim BreakDate As Variant
Dim NewDate As Date

BreakDate = Split("13/01/2010", "/")
NewDate = DateSerial(BreakDate(2), BreakDate(1), BreakDate(0))

MsgBox NewDate

End Sub

See if can help, thx.

 

[xenou]

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 seems to be following the regional settings (your regional settings are day-month-year). Is it an option to change the regional settings to month-day-year?


Edit: just scoured this from a post:

To check/change you Computers Date Settings go to:
Control Panel>Regional and Language Options
If you see English (United Kingdom) like in my case then 12/21/2009 will not be recognised as a date but as a text and must be typed in as 21/12/2009.
If the regional settings is English (United States) then all your dates must start with Months.

http://www.mrexcel.com/forum/showthread.php?t=459888

 

[xenou]

I probably won't catch any replies until later tomorrow so I'll add a quick note:

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

The use of Range("A1").Value vs. Range("A1") works the same because Value is the default property. So they really both work exactly the same. That said, I typically make the .Value property explicit by typing it out - my guess based on what I see on the board is its about 50/50 whether or not experienced Excel VBA programmers would go one way or the other - so whatever seems best to you is fine.

If you still have problems and need a fix, I'd suggest you clean the dates up while they are still in the array, before writing the array to the worksheet (this is faster working with arrays in memory rather than with cells on the worksheet). One way you might want to format the date, as a string in memory, is in long date format:

So, if the date is:
12/31/2010

Convert it to:
December 31, 2010

You might like to use the formula suggested in the post prior to my last, but with a slight change:

Dim BreakDate As Variant
Dim NewDate As Date
Dim NewStringDate As Date

BreakDate = Split("13/01/2010", "/")
NewDate = DateSerial(BreakDate(2), BreakDate(1), BreakDate(0))
NewStringDate = Format(NewDate, "long date")

Then switch out the array value from its current value to the long date. I've never actually tested out this theory, but I think that if you give Excel the long date, it will correctly understand it, no matter what the local settings - the display should be in the local setting, but the underlying value should be the correct date.

 

[Default300]

Typo
Before I say anything, there's a small but important typo in my original post above:

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

Should read:

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

Thanks
@Stephen_Tsui & @xenou
Thanks for your replies. I might give those workarounds a go if I can't work out how to prevent the problem from happening in the first place. I appreciate your help problem solving, to get me "unstuck". Cheers.


Current Workaround
@ALL
For the moment, I've gone with something similar (ie storing the date as 3 components). Then writing the 3 components to the Worksheet. Then using the Excel Formula Date (Year,Month,Day) to combine the relevant cells in another column. (As you no doubt know, Date is the Worksheet Function equivalent of VBA's DateSerial that you both suggested. Thanks again)

As it happens, I already have a row of cells on the Source Worksheet that contain the day/date number (without month or year). I also have the month of the Worksheet stored as a (Local) Named Range; and the year for the Workbook stored as a (Global) Named Range. So I don't have to do the splitting part of the solutions you suggested, just the join.

Even though it may seem (or be) inefficient, I've decided to add two new "Fields" to the 2D array to store the month and year for each "Record". This means a great deal of redundant data, but it seems more systematic to me and posssibly more easily extensible in the future, should I need to deal with multiple month or years simultaneously. Maybe I'm mad?!!

Transposing (and dropping terms "Original" and "Current" Array)

I've tried a different approach with transferring the array to the Worksheet. Previously I Transposed the "Original Array" into the Array I referred to as the "Current Array" in the previous post. The new approach is to Transpose the "Original Array" directly to the Worksheet.

Code for the transfer is as follows:

[FONT=Courier New]Let rngDestinationRange.Value = _
     Application.WorksheetFunction.Transpose(varContactRecords())[/FONT]

All tests below were done with this approach. From now on, as there will just be one Array, I will just refer to it as the Array (not "Original", "Current" or "Transposed" etc.)

Unfortunately, the outcome with this approach is exactly the same.

I've also tried Not Transposing when transferring (horizontal orientation). Same Result.

I've tried transferring to a new, unformatted sheet. No luck.


Array Properties, and Orientation

I've checked the DataType of this Array (as a whole). (By the way, I think the properties of the Transposed Array were different, but I won't discuss them as it's no longer in use.)

IsArray(varContactRecords()) = True
TypeName(varContactRecords()) = Variant()
VarType(varContactRecords()) = 8204

ie: 8192 (vbArray) + 12 (vbVariant) = 8204

It is a 2D Array.
Dimension 1 represents Fields.
Dimension 2 represents Records.

I know Dimension 1 is more typically used for Rows or Records and Dimension 2 for Columns or Fields. I did it this way for a couple of reasons, one of which is that I may well want to resize the Records Dimension but definitely won't want to resize the Fields Dimension. I believe ReDim Preserve can only be applied to the last Dimension.

Both Dimensions are 1 based.

But I use always use LBound and UBound when referring to it.


Regional Settings / Defaults (mentioned before)

In Control Panel / Regional and Language Options:
Short Date: "dd/MM/yyyy" (in Windows notation)
Long Date: "dd MMMM yyyy"
Both are the default for my region.

In Excel UI Cell Format dialog:
Locale: English(Ireland)

NB: All Dates in the Worksheet in question are in February.

Aarghh!
Still pretty annoyed and perplexed about why I can't just read the date in and write it back out without it getting "broken" though...


Additional Testing

NB: The relevant Column or Field of the Array is index 4 in Dimension 1.

As I loaded Source Worksheet Cell into the Array, Debug.Print gave the following results.

Example (1) Record 1

Range.Value = 01/02/2010
Range.NumberFormat = m/d/yyyy
IsDate(Range) = True

varContactRecords(4,1) = 01/02/2010
IsDate(varContactRecords(4,1)) = True
TypeName(varContactRecords(4,1)) = Date
VarType(varContactRecords(4,1)) = 7 (ie VBDate)

Example (2) Record 385

Range.Value = 18/02/2010
Range.NumberFormat = m/d/yyyy
IsDate(Range) = True

varContactRecords(4,385) = 18/02/2010
IsDate(varContactRecords(4,385)) = True
TypeName(varContactRecords(4,385)) = Date
VarType(varContactRecords(4,385)) = 7 (ie VBDate)

I'm suprised by VBA's interpretation of the NumberFormat of the Source Cells, because as I said, it is formatted as "*14/03/2001" as observed through the Excel UI Cell Format dialog.

I also can't see why it might want to default to "m/d/yyyy". (See "Regional Settings" paragraph).

I get the same result if I explicitly custom format the cell "dd/mm/yyyy" through the Cell Format dialog.

If I format the cells as "14/03/2001" (without the asterisk) then Range.NumberFormat returns "dd/mm/yyyy;@", rather than "m/d/yyyy", but output to the Destination Sheet is the identical.


I get a slightly different result if I attempt to Format the Array Element as "dd/mm/yyyy" as it is created (ie as Cell Value is loaded into Array). The only difference is that TypeName is now String and VarType is 8. IsDate is still True...

Example (3) Record 1

Range.Value = 01/02/2010
Range.NumberFormat = m/d/yyyy
IsDate(Range) = True

varContactRecords(4,1) = 01/02/2010
IsDate(varContactRecords(4,1)) = True
TypeName(varContactRecords(4,1)) = String
VarType(varContactRecords(4,1)) = 8 (ie VBString)

Example (4) Record 385

Range.Value = 18/02/2010
Range.NumberFormat = m/d/yyyy
IsDate(Range) = True

varContactRecords(4,385) = 18/02/2010
IsDate(varContactRecords(4,385)) = True
TypeName(varContactRecords(4,385)) = String
VarType(varContactRecords(4,385)) = 8 (ie VBString)


Also there is no difference when the Array is later written out the Destination Worksheet.

Dates/Days: 01 to 12
Cell(A1) Value: 02/01/2010
Cell(A1) Format: Date (Automatically, presumably interpreted as Months 1-12)
Cell(A1) Alignment: Right

Tests On Destination Cell
Cell(B1) Formula: =ISNUMBER(A1)
Cell(B1) Result: TRUE
Cell(C1) Formula: =TYPE(A1)
Cell(C1) Result: 1

Dates/Days 13 to 31
Cell(A1) Value: 18/02/2010
Cell(A1) Format: General(Automatically, presumably interpreted as Text)
Cell(A1) Alignment: Left

Tests On Destination Cell
Cell(B1) Formula: =ISNUMBER(A1)
Cell(B1) Result: FALSE
Cell(C1) Formula: =TYPE(A1)
Cell(C1) Result: 2

 

[Default300]

Quick One!

All dates in Worksheet in question are in February.

Therefore correct month for results is February.

If not clear, desired Format is dd/mm/yyyy, same as Source cells, my Windows default and my Excel's default.

Thanks.

 

[xenou]

Don't know why but in one case, changing the regional setting to something else and then back to your desired setting has worked:
http://www.mrexcel.com/forum/showthread.php?p=2237896

some more interesting information here on dates:
http://www.mrexcel.com/forum/showthread.php?p=2237896

 

[Default300]

Thanks & Regional Settings
@xenou

Regarding the link(s) you posted. Thanks. I take it you meant to post two though. Unfortunately they are both the same!! Cheers anyway.

I haven't tried the change and reset Regional Settings approach yet. I will do. I'm not too hopeful though, as I get the same behaviour on my work and home computers.
Unless perhaps some of the settings travel with the file rather then the application installation.

Home: Windows XP Home / Excel 2003 SP3 (11.8316.8221) / VBA 6.5
Work 1: Windows XP Pro / Excel 2003 SP3 (11.8316.8221) / VBA 6.5
Work 2: Windows XP Pro / Excel 2002 SP3 (10.6854.6856) / VBA 6.3

To be honest, I'm actually hoping that this is not the solution, because if it is then I might have to carry out that action for every computer and/or employee windows profile that I want to use the file! Not an option. Still, I will try it.


More Tests
(Test 1)
I've tested my default date format, by selecting a cell and pressing CTRL+ ;
The result is that today's date is inserted, in the format dd/mm/yyyy ie 23/06/2010.
This is the desired format.

(Test 2)
With the Destination Cells, if I enter the Cell (by F2 or double click) and then press Enter, then these are the results:

Dates/Days: 01 to 12

No Changes

In summary:
Cell correctly interpreted as a Date
Value is the wrong Date (month and day/date positions were reversed when cell value was written to sheet from array, compared to Source Cell Value)
Number Format is as desired (*14/03/2001) (ie dd/mm/yyyy appearance) (but since value is incorrect, equations dependent on this cell return wrong month or date)

Dates/Days: 13 to 31

Cell(A1) Value: 18/02/2010 (No Change)
Cell(A1) Number Format: Date (*14/03/2001) (ie dd/mm/yyyy appearance) (Changed: Previously General, presumably interpreted as Text)
Cell(A1) Alignment: Right (Changed: Previously Left)

Tests On Destination Cell
Cell(B1) Formula: =ISNUMBER(A1)
Cell(B1) Result: TRUE (Changed: Previously FALSE)
Cell(C1) Formula: =TYPE(A1)
Cell(C1) Result: 1 (Changed: Previously 2)

In summary:
Value is correctly interpreted as a Date
Value is the correct date (same as Value as Source Cell)
Number Format is as desired


Queries
(Query 1)
This makes me wonder would things work better if I transferred the Array to the Worksheet using a cell by cell Loop, rather than Assign the Array to the Destination Range?

(Query 2)
If I stick with Assigning it to the Range, have I got the syntax right?
Should I use Let or Set?
(btw I'm aware that Let is optional / inadvisable, given .Net regulations)

(Query 3)
Is it possible to test the Number Format an Array Element? I tried:

Debug.Print varContactRecords(4,1).NumberFormat

but this gives an Error: Object Required


Thanks again to anyone who can help.

 

[Default300]

Hi. After hours of searching and testing, I've finally found 2 solutions (below).

(Test3)
@xenou
Thanks for the suggestion from the post you found re change the Regional Settings. I tried it alright, but it didn't work for me. Must've been because (for the reasons mentioned in the last post) I had my fingers crossed that it wouldn't!

(Test4)
I had wondered if the following might have been a factor in the problematic behaviour, but testing suggests that it is not.

The first Array Element created in the Array "Field" in question is 01/02/2010. I reasoned, if VBA does not know what the Number Format is, (ie Debug.Print reports it to be "m/d/yyyy" even though it is "dd/mm/yyyy") then perhaps it is guessing that I mean January 2nd instead of 1st of February. But then to test it, I changed the first element to an unambiguous, eg 14/02/2010, but it still did not guess the Number Format correctly.

(Test5)
I wondered if the fact that the 5 "Fields" of the Array contained various different DataTypes was causing a problem. So I tried loading just the problematic Range into the Array. I also Dimensioned the 2D Array to just 1 Cell (ie "Field") "Wide" (rather than 5).

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

I then Assigned this Array to a single Column Range on the Destination Sheet, but the same problem occurred.


SOLUTION 1
I've tested transferring the problematic Field of the Array to the Range using a Loop (Element by Element / Cell by Cell), rather assigning the entire Array to the entire Range.

It Works!

That is, all Cells in the Destination Range have the correct Date, desired Number Format, alignment etc. and "test positive" for being a number etc.

It is good news that I can transfer data back out of the Array and have the Destination Cells appear and behave as expected / desired.

It is bad news that this solution requires a Loop to do so, rather than just assigning the Array to the Range, since Loops are reported to be slower, especially for larger datasets.


SOLUTION 2

This evening, I found the solution that I will go with, I think (unless I receive advice to the contrary, or a better solution, or God forbid, find there's another unforseen problem).

I got the tip-off from this link:
http://www.excelforum.com/999129-post3.html and then VBA's Help Files.

Now, instead of reading the Value Property of the Source Range Cells, I read the Value2 Property, and Load this into that "Field" of the Array. This stores the Value as a Double (double-precision floating-point) rather than a Date. Same memory required (8 Bytes).

I can return to the fast and simple method of Assigning the Array to the Destination Range. (I do not need to use a Loop).

Almost everything is as desired. Number Formating does not automatically change to date. If formatted as General, it will stay as General. However, if the Destination Range is Formatted as a Date (through the Excel UI or VBA), (before or after the Range is transferred from the Array), then the number will automatically be displayed as a date in the format you choose.

Example (1) Record 1

Range.Value = 01/02/2010
Range.NumberFormat = m/d/yyyy
IsDate(Range) = True

varContactRecords(4,1) = 40210
IsDate(varContactRecords(4,1)) = False
TypeName(varContactRecords(4,1)) = Double
VarType(varContactRecords(4,1)) = 5 (ie vbDouble)

Example (2) Record 385

Range.Value = 18/02/2010
Range.NumberFormat = m/d/yyyy
IsDate(Range) = True

varContactRecords(4,385) = 40227
IsDate(varContactRecords(4,385)) = False
TypeName(varContactRecords(4,385)) = Double
VarType(varContactRecords(4,385)) = 5 (ie vbDouble)

Dates/Days: 01 to 31

Cell(A1) Value: 01/02/2010 (same for 18/02/2010)
Cell(A1) Number Format: Date (*14/03/2001) (ie dd/mm/yyyy appearance)
Cell(A1) Alignment: Right

Tests On Destination Cell
Cell(B1) Formula: =ISNUMBER(A1)
Cell(B1) Result: TRUE
Cell(C1) Formula: =TYPE(A1)
Cell(C1) Result: 1

In summary:
(once Destination Range is Formatted as Date)
Value is correctly interpreted as a Date
Value is the correct date (same as Value as Source Cell)
Number Format is as desired


Other Resources
Although I didn't use this next link in the end, if other people are having problems with dates, it looks like a great resource: [ http://www.mvps.org/dmcritchie/excel/datetime.htm ] (initially written in 1998, but updated recently).

Thanks a million again to those who helped brainstorm. Hopefully, this solution will help others. But as always please let me know if I'm heading for trouble with this approach!!

Cheers!

 

[Bea79]

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