I have strings in the format dd.mm.yyyy which I want to convert to
dates. ie 07.11.2007 is 7th Nov 2007.I'm in the UK, with UK regional
settings.


I tried Cdate which didn't work. I then used find and replace to
replace the " ." with a "/" as follows


Range("c" & i).Replace What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False

The results are strange.

15.05.2007 changes to 15/05/2007 and if I check with ISNUMBER=False
( I can change to date with Cdate)

04.08.2007 changes to 08/04/2007 (8th of April when date was 4th Aug)
and if I check with ISNUMBER=True.

It seems when I do the find and replace with a "/" dates which are
legimitate as mm/dd/yyyy are conveted to a date number and other dates
just looking like dd/mm/yyyy are not converted to a date number.

Can anyone give me some pointers how to easilt change a string
dd.mm.yyyy to a date. I'm struggling with this.

Thanks

Ian, Try this:
Sub ConverttoDate()
Dim s1 As String
Dim d1 As Date
Dim sAr() As String
s1 = "7.11.2007"
sAr = Split(s1, ".")
d1 = DateSerial(sAr(2), sAr(1), sAr(0))
Debug.Print d1
End Sub

--
Charles Chickering

"A good example is twice the value of good advice."


"ian" wrote:

I have strings in the format dd.mm.yyyy which I want to convert to
dates. ie 07.11.2007 is 7th Nov 2007.I'm in the UK, with UK regional
settings.


I tried Cdate which didn't work. I then used find and replace to
replace the " ." with a "/" as follows


Range("c" & i).Replace What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False

The results are strange.

15.05.2007 changes to 15/05/2007 and if I check with ISNUMBER=False
( I can change to date with Cdate)

04.08.2007 changes to 08/04/2007 (8th of April when date was 4th Aug)
and if I check with ISNUMBER=True.

It seems when I do the find and replace with a "/" dates which are
legimitate as mm/dd/yyyy are conveted to a date number and other dates
just looking like dd/mm/yyyy are not converted to a date number.

Can anyone give me some pointers how to easilt change a string
dd.mm.yyyy to a date. I'm struggling with this.

Thanks

Charles

Great, simple and it works perfeclty.

Thanks