How can this be???

[kirkm]

I'm reading the contents of a Cell - formatted as custom "dd mmm
yyyy".

The text in the Cell is "08 Mar 1980".

Reading the cell magically converts it to 8/03/1980. This is done when
referencing it via Cells(line, column).

Writing this to another sheet via

Sub WriteSheet(mVal, mL, mC)
With Worksheets("Sheet3")
..Cells(mL, mC) = mVal
End With
End Sub

Sees the cell contents become 29288.

This really happens!!! And I've done hundreds of tests.... crazy !
What could possible cause it? I've varies the formatting of the
destination cell to text, and General. No change.

Why doesn't it read what it is - and write the same thing? Isn't this
the whole point ?

Thanks - Kirk

 

[macropod]

Hi kirkm,

The result you're getting is because Excel stores dates as numbers. The number 29288 is the number of days since 31/12/1899, which
corresponds with 8/03/1980.

All you need to do is to format the destination cell in the required date format. You can do this manually or in code.

--
Cheers
macropod
[Microsoft MVP - Word]


in message news:[email protected]...

 

[FSt1]

hi
you have a format problem. your code is carring the cell value over but not
the formating becasue 29288 is the serial date number for march 8th 1980.
you may have mVar dim'ed as a long or integer. dim it as a date
or
you could format it in your code....
.Cells(mL, mC) = Format(mVal, "mm/dd/yy")

either way would work.
this is how excel keeps track of and views dates. serial date number 39943
is todays date or 39,943 days from 1/1/1900. serial date nuber 29288 is
3/8/80 and is 29,288 days from 1/1/1900.

so it's not a bunch of hocus pocus stuff. it's ........just formatting.

regards
FSt1

 

[kirkm]

Thanks to you both for answering. At least that number
has a meaning and explanation. I suppose I'm too pratical
where 'text' is just text and you don't anticpate some change
you haven't asked for.

Yes, I corrected it with Format... which was ironic as it
was formtted initially... haha, perhaps it's a reformat

Cheers - Kirk

 

[JoeU2004]

I suppose I'm too pratical where 'text' is just
text and you don't anticpate some change
you haven't asked for.

Your comment suggests to me that you did not fully understand the responses.

You don't have text; you have a number. You formatted that number with a
custom date format.

When you do:

..Cells(mL, mC) = mVal

you are assigning only the cell value, the number. You neglected to specify
the format.

If you want to control the format, you also need:

..Cells(mL, mC).NumberFormat= "dd mmm yyyy"

More generally, if mVal is a cell reference (type Range), you can do:

..Cells(mL, mC) = mVal
..Cells(mL, mC).NumberFormat= mVal.NumberFormat

Yes, I corrected it with Format... which was ironic as it
was formtted initially... haha, perhaps it's a reformat

Actually, the irony is: it may have worked for you only because you
formatted the cell initially with the desired format.

It is unclear to me what Format() parameters you used. FSt1 had written
"mm/dd/yy". You had written: "Reading the cell magically converts it to
8/03/1980", which suggests that you might used "d/mm/yyyy". On the other
hand, if you wanted the original format, you might used "dd mmm yyyy".

But if Cells(mL,mC) had a General format initially, none of those would
result in their intended format simply by assigning Format() to the cell
value.

The first two formats result in the default Date format, the form of which
varies depending on localization. The last format results in the Custom
format d-mmm-yy (!).

(At least, that is the case for my localization settings. I have not tried
others.)

Cheers!


----- original message -----

 

[kirkm]

Hi Joe,

Thanks for the reply. Don't think I'm completely out of the woods
yet. The problem reading a value remains

The cell is formatted as Custom dd mmm yyyy
The contents, as typed in, are 08 Mar 1980.

I read it with this code:-

Sub ttt()
Dim d
d = ReadCell("TheSheet", "J4")
End Sub

Function ReadCell(msheet, mCell)
ReadCell = ThisWorkbook.Worksheets(msheet).Range(mCell).Value
End Function


The resulting value of d is 8/03/1980
or
if d$ is specified, it's "8/03/1980"

Why is it not "08 Mar 1980" (as expected) and why does it change?

Unless 'Custom' means 'Custom DATE", not custom FORMAT.

Writing values back, your .NumberFormat suggestion fixed that nicely.
Thanks.

Cheers - Kirk

 

[JoeU2004]

Dim d
d = ReadCell("TheSheet", "J4")
[....]
ReadCell = ThisWorkbook.Worksheets(msheet).Range(mCell).Value
[....]
The resulting value of d is 8/03/1980
[....]
Why is it not "08 Mar 1980" (as expected) and why does it change?
Unless 'Custom' means 'Custom DATE", not custom FORMAT.

You're close. Caveat: I am not familiar with all the vagaries of Excel and
VBA objects. What follows is my interpretation of what I observe. It might
not be entirely accurate.

Once again, you are confusing the appearance of a value with the actual
value itself, a number in this case.

When you access ".Value", you are accessing a variant object.

Apparently, the variant object ".Value" has a property that characterizes
the form of the value to some degree, for example currency, date, presumably
number and text, and perhaps others. I presume that VBA (or Excel)
characterizes the form of the value based on its interpretation of the Excel
format.

Refer to http://support.microsoft.com/kb/182812 for a partial explanation.
As the KB explains, the VBA form of a date value is m/dd/yyyy or d/mm/yyyy,
depending on localization settings.

If you had used type Long for "d" or "ReadCell", or if you had accessed the
".Value2" property, you would have seen, once again, that you are not
reading a particular string (e.g. 8/03/1980), but the number 29288, a serial
number in this case.

Within VBA, you can get the desired appearance by using Format(d,"dd mmm
yyyy").

Caveat emptor: Note that if you enter $1.23456789 into an Excel cell, the
".Value" property returns 1.2346, even if you assign it do a Double
variable. That is, 1.2346 is the actual number returned by ".Value", not
just its appearance. So it seems to be safer to access the ".Value2"
property, which returns 1.23456789, if you want to be sure to get the actual
number in a cell.


----- original message -----

 

[kirkm]

Thanks for the rundown Joe. That's shed a lot of light on the matter
and I must admit I'd never heard of the .Value2 property. Something to
remember.

There sure is a lot to all this

Cheers - Kirk