Odd date behaviour

[CMIIandExcel]

I have an issue when transfering a date from one spreadsheet to another

A UK format date is being changed to a US format date

The date is passed within a varible declared as :

Dim strExportDate As String

This date is passed ByVal to another Sub routine and then then called and entered into another workbook by;

shExport.Cells(3, 3) = strExportDate

The format of the cell the date is taken from is
DATE UK-Locale *dd/mm/YYYY

The format of the Cell where the date is then entered is
DATE UK-Locale dd/mm/YYYY

(note the *is the only difference)

Somehow the value is being changed from UK to US, eg 04/09/2009 from the source cell is becoming 09/04/2009 in the destination cell

Thanks in advance for any assitance

Mike

 

[Norie]

Are you 100% sure the value is being changed? Perhaps it's just the format.

If strExportDate try converting it to a true date using DateValue.

shExport.Cells(3, 3) = DateValue(strExportDate)

That should definitely give you a date, but again you might have to check the formatting.

 

[CMIIandExcel]

Norie

as far as i could see yes the value was being changed, however the DateValue statement worked a treat.

Thank you kindly

morals to this story

Simple solutions are the best
Never test a macro that transfers dates with the date 09/09/09