VBA Date Format For Variable

1
votes

I need to turn content in a spreadsheet column from text to a date.

The cell format is text and the inputters were instructed to input a date as "ddmmyyyy".

Accidents happened and I found some content that would not parse as a date, including entries like "Unknown".

So I used a variable declared as a date and wrote an error handler to deal with content that would not parse.

Now for the bit I cannot work out.

If the date was 3rd March 2000 and someone input that as "03332000" that will not parse because "33" cannot be a month or a day; it is caught by the error handler as I wanted.

But if it was input as "03132000" I can't think of a way of preventing VBA converting that to a valid date as "13/03/2000".

Declaring a format for the date variable will not prevent VBA parsing the date.

I can write something that tests number range of the day and month part of the string but that is extra lines of code and I was hoping to do it just by the error handler.

2
excelvbadateformat
Please show your existing code - much easier to tweak that than to have us try and guess exactly what you did. You can try using DateSerial() and parsing out the specific parts of the entry using Left()/Mid()/Right() - Tim Williams
Tim Thanks. I'll modify the post in a bit. - River42
Tim, by the time I had rewritten the code (I am at home now) @Gary's Student had confirmed that I have to test the components of the date for validity. - River42
I wasn't suggesting you don't need to test for validity, just that you could prevent Excel from swapping the day and month if it felt like it. - Tim Williams

2 Answers

1
votes

I'd approach it a little differently and let Excel do the work.

Public Function ValidateDate(ByVal strDate As String) As Boolean
    Dim intDay As Integer, intMonth As Integer, intYear As Integer, dtDate As Date

    ValidateDate = True

    On Error GoTo IsInValid

    If Len(strDate) <> 8 Then GoTo IsInValid
    If Not IsNumeric(strDate) Then GoTo IsInValid

    intDay = Left(strDate, 2)
    intMonth = Mid(strDate, 3, 2)
    intYear = Right(strDate, 4)

    dtDate = DateSerial(intYear, intMonth, intDay)

    If DatePart("d", dtDate) <> intDay Then GoTo IsInValid
    If DatePart("m", dtDate) <> intMonth Then GoTo IsInValid
    If DatePart("yyyy", dtDate) <> intYear Then GoTo IsInValid

    Exit Function

IsInValid:
    ValidateDate = False

End Function

... this will ensure that anything related to leap years etc. will still work correctly and it will ensure that all entries are validated correctly.

enter image description here

0
votes

If you place:

03332000

in cell A1 and run:

Sub CheckDate()
    Dim s As String, d As Date
    s = Range("A1").Text
    d = DateSerial(CInt(Right(s, 4)), CInt(Mid(s, 3, 2)), CInt(Left(s, 2)))
    MsgBox s & vbCrLf & d
End Sub

You will get:

enter image description here

So even though a valid month can only be in the range [1-12], Excel is trying to "help" you by interpreting the 33 as a projection of future date. For example, if the month was entered as 13, Excel will treat it as December of the following year!

You can't rely on error-handling for this. You need checks like:

Sub CheckDate2()
    Dim s As String, d As Date
    Dim dd As Integer, mm As Integer, yr As Integer

    s = Range("A1").Text

    yr = CInt(Right(s, 4))
    mm = CInt(Mid(s, 3, 2))
    dd = CInt(Left(s, 2))

    If yr = 0 Or yr < 1900 Then
        MsgBox "year is bad"
        Exit Sub
    End If

    If dd = o Or dd > 31 Then
        MsgBox "day is bad"
        Exit Sub
    End If

    If mm = 0 Or mm > 12 Then
        MsgBox "month is bad"
        Exit Sub
    End If

    d = DateSerial(yr, mm, dd)
    MsgBox s & vbCrLf & d
End Sub

You can also do other checks like looking at the length of the field, etc.