CODEWITHSUNDEEP
excelvbadateformat
River42
River42

Reputation: 13

VBA Date Format For Variable

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.

Upvotes: 1

Views: 540

Answers (2)

Skin
Skin

Reputation: 11197

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

Upvotes: 1

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

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.

Upvotes: 0

Related Questions