Dates converting to US format

[theFISman]

Hi,

I have a date format/time of '15/06/200919:17:15' extracted from another system. I usually drop the time using the following macro (which has worked in the past):

x = 1
Do Until x = 150
x = x + 1
Dim chngdte1
Range("p" & x).Select
chngdte1 = ActiveCell.FormulaR1C1
chngdte1 = Mid(chngdte1, 1, 10)
Range("p" & x).Select
ActiveCell.FormulaR1C1 = chngdte1
Loop

But for some reason the dates now change from UK to US format? Any ideas?

Also I would actually like to automatically put a space in between the date and time (excel will then accept it as a date format) using a macro...any help her also appreciated

Many thanks, Steve

 

[Andrew Poulsom]

Try:

chngdte1 = DateValue(Mid(chngdte1, 1, 10))