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


---
Message posted from http://www.ExcelForum.com/

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.

--
Michael J. Malinsky


"andycharger " wrote in 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


---
Message posted from http://www.ExcelForum.com/

-----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


---
Message posted from http://www.ExcelForum.com/

.


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")


-----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


---
Message posted from http://www.ExcelForum.com/

.