I have found that using a macro to set a cell value to a date does not
generate the expected result. Consider ...

Sub mcrSetDate()
ActiveCell.Value = "1/2/2003"
ActiveCell.NumberFormat = "dd mmm yyyy"
End Sub

This results in "02 Jan 2003". However if I enter "1/2/2003" into a cell
directly and format it I get "01 Feb 2003". Clearly VBA is not observing
the system date/time format which is set to Australian. (ie. dd/mm/yy) but
it is clearly parsing the .Value to a date serial.

Changing the statement to the following corrects the problem...

ActiveCell.Value = DateValue("1/2/2003")

However this means because my macro is dealing with data of unknown type
(from a text file) I have to inspect every value to decide if it is a date
so I can cast it before assigning it to the .Value.

Does anyone know of a cleaner way to get around this problem. The sheet
involved is always new and has never had any formatting aplied by the time
the data is being placed on it.

Cheers, Frank.