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

    Can't get a date (VBA XL2000)

    <img src=/S/confused.gif border=0 alt=confused width=15 height=20> Pardon my ignorance, but O'Reilly's VB & VBA in a Nutshell tells me that FormatDateTime returns a string, yet when I turn this back into a sub, use a valid filename and "MsgBox TypeName(GetFileDate)", it returns Date.

    Function GetFileDate(strFPath As String, strFName As String) As Date
    GetFileDate = Date
    If Len(Dir(strFPath & strFName, vbDirectory)) > 0 Then _
    GetFileDate = FormatDateTime(FileDateTime(strFPath & strFName), vbShortDate)
    End Function

    Is this function valid or am I returning a string? If I'm returning a string, am I going to have problems comparing it to another date generated the same way?
    -John ... I float in liquid gardens
    UTC -7ąDS

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't get a date (VBA XL2000)

    FormatDateTime does return a string. However, you have defined your function as returning a Date. Therefore, when you set GetFileDate equal to the result of the FormatDateTime, the string is converted to a date.
    Legare Coleman

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Can't get a date (VBA XL2000)

    VBA is doing some kind of implicit type conversion from the string created with FormatDateTime to the Date returned by the function. Probably makes more sense not to convert to string unless you want to flush the time information...playing around in the Immediate Window yielded this:

    ?now
    9/25/2002 7:29:44 PM
    ?formatdatetime(now)
    9/25/2002 7:29:55 PM
    ?typename(formatdatetime(now))
    String
    ?typename(cdate(formatdatetime(now)))
    Date
    ?cdate(formatdatetime(now))
    9/25/2002 7:30:40 PM
    ?cdate(formatdatetime(now,vbShortDate))
    9/25/2002
    ?formatdatetime(cdate(formatdatetime(now,vbShortDa te)),vbGeneralDate)
    9/25/2002
    ?formatdatetime(cdate(formatdatetime(now,vbShortDa te)),vbLongTime)
    12:00:00 AM
    ?cdate(int(now))
    9/25/2002
    ?formatdatetime(cdate(int(now)),vbLongTime)
    12:00:00 AM

    The last two lines show that using the Int function will also nuke the time portion of the value.

    (I've probably digressed to the point of not being useful, but I'll leave this here for amusement value.)

  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 a date (VBA XL2000)

    Thanks guys; I'll use both the implict conversion and the int() conversion as in:

    GetFileDate = Int(FileDateTime(strFPath & strFName))
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't get a date (VBA XL2000)

    The function returns a Date converted to a string. It is actually a Variant (String), see the online Help.

    Debug.Print FormatDateTime(FileDateTime(ThisDocument.FullName) )
    Debug.Print VarType(FormatDateTime(FileDateTime(ThisDocument.F ullName)))

Posting Permissions

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