Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Checking for valid date (WinNT/Word97)

    Is there a way to check that the entry made in a field is a valid date? By which I mean not that it's laid out as a date but that it IS a date, eg 13/13/2003 isn't because there are only 12 months in the year.

    I have a user form where the entry needs to be a date in the format "dd/mm/yyyy" (because it's later assigned to a custom property that is a date), but while testing I discovered that if you enter something that isn't, like the 13/13/2003 above, the sub just crashes when it tries to assign to the property.

    Any help would be gratefully appreciated!
    Beryl M


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

    Re: Checking for valid date (WinNT/Word97)

    Use the IsDate function.

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Checking for valid date (WinNT/Word97)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> Sheesh! It's seems so easy when you know, doesn't it?! <img src=/S/woops.gif border=0 alt=woops width=58 height=36>

    And yet I couldn't even come up with anything I could search on to find it! <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> Many thanks!
    Beryl M


  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checking for valid date (WinNT/Word97)

    Not a bad function, but not entirely accurate. It returns True for "September 8, 1752" - a date which didn't actually exist, at least if you lived in the English-speaking world. Probably too long ago to worry about though. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Alan

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Checking for valid date (WinNT/Word97)

    An interesting point, although the help file does specify that it will only deal with dates between January 1, 1980 through December 31, 2079 on WinNT, 2099 on other windows (don't ask me where they got the dates from!).

    Having said that, my sub is dealing with review dates from policies and procedures, so (1) if it needed reviewing that long ago it is seriously out of date, and (2) if the review date is after 2079 it's a fair certainty it doesn't really need reviewing (along the lines of "The Sun will come up in the East" and "Things put in water get Wet")!!!
    Beryl M


  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Marietta, Georgia, USA
    Posts
    296
    Thanks
    9
    Thanked 4 Times in 4 Posts

    Re: Checking for valid date (WinNT/Word97)

    There are some known issues with the IsDate function. Depending on the data you pass it, it may not deliver reliable results. This article discusses a problem that affects users in Australia, as well as a solution:
    http://www.ausdev.net/dotmag/art-y2K4VB.asp

    That article references a Microsoft discussion of known problems with IsDate:
    http://support.microsoft.com/default.aspx?scid=kb;[LN];Q184521

    This article clarifies that the VBA version of the IsDate function tries to be a little TOO helpful. You may encounter a situation that can best be described by "It's NOT a bug, it's a FEATURE!"

    Here is the text at the end of the Microsoft article:
    "Expressions that use other formats (such as year-day-month) are not recognized as dates by Microsoft Excel. However, because of the design of the IsDate function, it returns True for any expression that contains a year, a month, and a day, in any order."
    Rick Groszkiewicz
    Life is too short to drink bad wine (or bad coffee!)

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

    Re: Checking for valid date (WinNT/Word97)

    Search for relevant words in the Help and object browser.

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

    Re: Checking for valid date (WinNT/Word97)

    Create a Userform named frmOutput with a Listbox named lstOutput and see whether you can run the following code.

    I ran the code in VB 6.

    <pre>
    Private Sub MoreDates()
    Dim strDate As String

    strDate = Now
    GetDateFormats strDate
    strDate = "2002 5 July"
    GetDateFormats strDate
    strDate = "2002, 5 July"
    GetDateFormats strDate
    strDate = "2002 5, July"
    GetDateFormats strDate
    strDate = "2002 5 July,"
    GetDateFormats strDate

    strDate = "2002 July 5"
    GetDateFormats strDate
    strDate = "2002, July 5"
    GetDateFormats strDate
    strDate = "2002 July, 5"
    GetDateFormats strDate
    strDate = "2002 July 5,"
    GetDateFormats strDate

    strDate = "July 5 2002"
    GetDateFormats strDate
    strDate = "July, 5 2002"
    GetDateFormats strDate
    strDate = "July 5, 2002"
    GetDateFormats strDate
    strDate = "July 5 2002,"
    GetDateFormats strDate

    strDate = "July 2002 5"
    GetDateFormats strDate
    strDate = "July, 2002 5"
    GetDateFormats strDate
    strDate = "July 2002, 5"
    GetDateFormats strDate
    strDate = "July 2002 5,"
    GetDateFormats strDate

    strDate = "5 2002 July"
    GetDateFormats strDate
    strDate = "5, 2002 July"
    GetDateFormats strDate
    strDate = "5 2002, July"
    GetDateFormats strDate
    strDate = "5 2002 July,"
    GetDateFormats strDate

    strDate = "5 July 2002"
    GetDateFormats strDate
    strDate = "5, July 2002"
    GetDateFormats strDate
    strDate = "5 July, 2002"
    GetDateFormats strDate
    strDate = "5 July 2002,"
    GetDateFormats strDate

    strDate = "30/4/01"
    GetDateFormats strDate
    strDate = "30/01/4"
    GetDateFormats strDate
    strDate = "4/30/01"
    GetDateFormats strDate
    strDate = "4/01/30"
    GetDateFormats strDate
    strDate = "01/30/4"
    GetDateFormats strDate
    strDate = "01/4/30"
    GetDateFormats strDate

    strDate = "30/4/2001"
    GetDateFormats strDate
    strDate = "30/2001/4"
    GetDateFormats strDate
    strDate = "4/30/2001"
    GetDateFormats strDate
    strDate = "4/2001/30"
    GetDateFormats strDate
    strDate = "2001/30/4"
    GetDateFormats strDate
    strDate = "2001/4/30"
    GetDateFormats strDate
    End Sub

    Private Sub GetDateFormats(strDate As String)
    On Error Resume Next
    With frmOutput
    .Show
    With .lstOutput
    .AddItem "Input: " & strDate
    .AddItem vbTab & "Valid date? " & IsDate(strDate)
    .AddItem vbTab & "CDate: " & CDate(strDate)
    .AddItem vbTab & "Medium Date: " & Format(strDate, "Medium Date")
    .AddItem vbTab & "Short Date: " & Format(strDate, "Short Date")
    .AddItem vbTab & "Long Date: " & Format(strDate, "Long Date")
    .AddItem vbTab & "General Date: " & Format(strDate, "General Date")

    .AddItem vbTab & "Short Date: " & FormatDateTime(strDate, vbShortDate)
    .AddItem vbTab & "Long Date: " & FormatDateTime(strDate, vbLongDate)
    .AddItem vbTab & "General Date: " & FormatDateTime(strDate, vbGeneralDate)
    End With
    End With
    On Error GoTo 0
    End Sub
    </pre>


  9. #9
    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: Checking for valid date (WinNT/Word97)

    Personally, I like CDate(). But no matter how you slice it, Windows is going to apply certain regional "assumptions" when parsing date strings. In the U.S., these examples give the expected results, but shorted the year portion of the dd/mm/yyyy dates and they are interpreted as yy/mm/dd. So... your mileage may vary.
    <pre>Function CleanDate(ByRef strDate) As Boolean
    ' Returns True for valid dates (and reformats the input string), False for invalid dates
    Dim dateFromString As Date
    On Error Resume Next
    dateFromString = CDate(strDate)
    If Err.Number = 13 Then ' Type Mismatch
    Err.Clear
    On Error GoTo 0
    CleanDate = False
    Else ' Try it!
    On Error GoTo 0
    CleanDate = True
    strDate = Format(dateFromString, "mm/dd/yyyy")
    End If
    End Function

    Sub TestCleanDate()
    Dim strTestDate As String
    strTestDate = "01/20/2004"
    Debug.Print CleanDate(strTestDate), strTestDate
    strTestDate = "3/3/03"
    Debug.Print CleanDate(strTestDate), strTestDate
    strTestDate = "29/02/2004"
    Debug.Print CleanDate(strTestDate), strTestDate
    strTestDate = "29/02/2001"
    Debug.Print CleanDate(strTestDate), strTestDate
    strTestDate = "hot cocoa"
    Debug.Print CleanDate(strTestDate), strTestDate
    End Sub</pre>

    Note: Tested in Word 2002 VBA.

  10. #10
    New Lounger
    Join Date
    Jan 2004
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checking for valid date (WinNT/Word97)

    I too have had fun and games with date formats in VBA due to the system often assuming that dates will be in US format mm/dd/yy. I spent an enjoyable hour or so tracing a date through a script to find that the problem came when the date was inserted back into the Excel cell. This is the line where the problem came:

    wsReport.Cells(myCurrentRow, 2) = Jobdetail

    Debug the script and within VBA, if jobdetail is a date then the format was dd/mm/yy. The worksheet (wsReport) was using dd/mm/yy too. But when I looked at the results the date format was dd/mm/yy but the data had the day and month swap round. So 10/8/03 (dd/mm/yy) would become 8/10/03 (dd/mm/yy) in the sheet. To get it to work I had to force the date into a format that Excel/VBA coundn't confuse:

    If IsDate(Jobdetail) = True Then
    wsReport.Cells(myCurrentRow, 2) = Format(Jobdetail, "dd mmm yyyy")
    Else
    wsReport.Cells(myCurrentRow, 2) = Jobdetail
    End If

Posting Permissions

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