Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can't get my dates right! (XL2003)

    Hi.

    Got a problem which is, I suspect, linked to VBA's irritating insistence that all dates are only ever American, but what is really really hacking me off is that I can do the same thing manually in Excel and get it to work, but when I use VBA it dies a horrible death.

    I have some text files which contain dated records. Since they come from my European partners, they use a period "." separator for the date. Me, being British, I have a "/" separator. We both agree, though, that the format is DD/MM/YYYY. If I import the file using the "OpenText"method, then Excel says it doesn't know about the format and writes it as text. Fair enough.

    BUT

    If I now replace all instances of "." with "/" manually in Excel, it converts them all to dates and "01.08.2004" [first August, for all you North Americans] becomes "01/08/2004".
    Doing the same thing in VBA, though, it turns "01.08.2004" into "08/01/2004" which as far as I am concerned is the 8th January. Irritatingly, it lets me keep "31/07/2004", which means it's not even being consistent.

    I've tried forcing it to be DMY using the FieldInfo parameter, but it thinks it knows best and changes it (a continuing irritation with importing CSV files, too, that I'll leave until another message!). I've tried formatting the cells as "DMY" format as well. My only option at the moment appears to be to read the value out of the file, physically MAKE it understand by converting the month number to text, and then writing it back again. I could, since it's not a big file, but why should I have to?

    Surely there's a better way? Please let me know!

    Cheers.

    Stuart

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Can't get my dates right! (XL2003)

    You'll have to parse the MM, DD & YY components and add them to a date Type variable, rather than allowing VB to implicitly convert. See the thread starting with <post#=206871>post 206871</post#>, and also <post#=196273>post 196273</post#>
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't get my dates right! (XL2003)

    Hurrah!

    Thanks, John. But seriously, what a palaver. What's the point of having an xlDMYFormat value if you ignore it!

    Yours in extreme frustration,

    Stuart

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Can't get my dates right! (XL2003)

    I know, I know.

    If you don't need compatibility with Excel versions prior to 2000, you may be able to simplify Jan Karel's code to something like (lightly tested):

    Sub CvddmmyyyyDate2Val()
    Dim rCell As Range
    Dim strA() As String
    For Each rCell In Intersect(Selection, ActiveSheet.UsedRange)
    On Error Resume Next
    If IsError(CLng(Replace(rCell.Value, ".", "", 1, -1, vbTextCompare))) Or Len(rCell.Value) <> 10 Then
    ' invalid date
    rCell.Value = DateValue("1/1/100") ' or whatever you want
    Else
    strA() = Split(rCell.Value, ".", -1, vbTextCompare)
    rCell.Value = DateSerial(strA(2), strA(1), strA(0))
    End If
    Next rCell
    Intersect(Selection, ActiveSheet.UsedRange).NumberFormat = "dd/mm/yyyy"
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •