Date and string help

  • Thread starter Thread starter andycharger
  • Start date Start date
A

andycharger

Hi,
Im writing a macro and im storing a date value form my spreadsheet in a
variable.

I had been doing this:

Dim strRenDate as String

strRenDate = Cells(RowDates, "E").Value

Now this worked ok EXCEPT when it pastes the date into another
spreadsheet, It seems to confuse the date and put it into an American
format (i.e instead of 08/01/2004 it says 01/08/2004)
This is despite me formatting both spreadsheet columns to read the date
as dd/mm/yyyy.

I guessed the best way to do it would be to store the variable as a
DATE rather than a string.

However, When I tried this, It said Type Mismatch.

Any idea how I get the value from my cell into a DATE style and write
it into another cell without this date reversing issue?

Thanks
 
Try formatting your data within the VBA code instead of formatting the data
in the worksheet. Something like:

strRenDate = Format(Cells(RowDates, "E").Value, "dd/mm/yyyy")

HTH
Mike.
 
-----Original Message-----
Hi,
Im writing a macro and im storing a date value form my spreadsheet in a
variable.

I had been doing this:

Dim strRenDate as String

strRenDate = Cells(RowDates, "E").Value

Now this worked ok EXCEPT when it pastes the date into another
spreadsheet, It seems to confuse the date and put it into an American
format (i.e instead of 08/01/2004 it says 01/08/2004)
This is despite me formatting both spreadsheet columns to read the date
as dd/mm/yyyy.

I guessed the best way to do it would be to store the variable as a
DATE rather than a string.

However, When I tried this, It said Type Mismatch.

Any idea how I get the value from my cell into a DATE style and write
it into another cell without this date reversing issue?

Thanks
Click to expand...

Use the DateValue() function to convert your string into a
date, e.g.:
RenDate = DateValue(Cells(RowDates, "E").Value)
 
I assume that the "other" spreadsheets are on the same
computer and, thus, the Windows level setting for dates is
the same; if not, that is likely your porblem.

I am no expert in this, but I have run into problems with
the way VBA handles dates and "string dates" and have
concluded that Excel VBA does several "funny" things when
you store dates in strings. I recommend two things:
1. use a date format that is unambiguous and can not be
confused. Using dd/mm/yy or mm/dd/yy is asking for
trouble. I have used dd-mmm-yyyy (today's date is 19-Jan-
2004) for over 30 years. This applies to more than just
Excel.
2. use the DATE type when possible. I have found that it
acts a bit more consistently then when you place date text
into a STRING variable.

The only method I have found that CONSISTENTLY gives me
the results I want is to format the receiving cell just
before I write the data text to it, for example

Dim strDate As String

strDate = Cells(4, 2)
Cells(4, 4) = strDate
Cells(4, 6).NumberFormat = "dd-mmm-yyyy"
Cells(4, 6) = strDate

My system is set up so that the default short date is dd-
mm-yy. Cells 4,4 and 4,6 are not formated. The resulting
format for cell(4,4) is dd-mm-yy. The resulting format
for cell(4,6) is dd-mmm-yyyy. This approach will work for
DATE type variables as well. I have found that if you do
not explicitly format the receiving cells, you get the
same results with DATE variables even if you use the
FORMAT command to deposit text into the cell, e.g.,

Dim dteTemp as Date

dteTemp = Cells(4, 2)
Cells(4,6) = FORMAT(dteTemp,"dd-mmm-yyyy")
 
Mike: I still argue that Excel/VBA does strange things
with dates in strings and that the Format statement will
not help. My experience is that the representation in the
string variable in VBA and the results when you write that
value to a worksheet cell are different. For example,
when I used your code and displayed the results of
strRenDate using MsgBox, the results were correct. But
when I then set a cell equal to strRenDate and looked at
the results, the format had been changed to the Window
default format for dates. It is as though Excel
recognizes that this string text is really a date and
automatically sets the receivng cell format to "date". If
I add some other text to the write, e.g.,

Cells(R,C) = "the date is " + strRenDate

the results are correct:


-----Original Message-----
Try formatting your data within the VBA code instead of formatting the data
in the worksheet. Something like:

strRenDate = Format(Cells
Click to expand...
(RowDates, "E").Value, "dd/mm/yyyy")
 
Back
Top