vba 验证日期格式

Validating date format

提问by kruk22

Anybody knows how to validate date format in the format of mm/dd/yyyy. I have tried this code but is not working.

任何人都知道如何以mm/dd/yyyy. 我试过这段代码，但没有用。

In the userform:

在用户表单中：

dateCheck (txtStartDate)

In module:

在模块中：

Function dateCheck(dateValue As Date) As Boolean
    If CDate(dateValue) <> "mm/dd/yyyy" Then
        MsgBox "Please use the mm/dd/yyyy  date format!"
        dateCheck = True
    End If
End Function

Am I on the right track? Thanks!

我在正确的轨道上吗？谢谢！

回答by Jordan

The CDate(dateValue)part of your function will simply return 'dateValue' as a Date. Use the .Numberformatproperty to get the format:

在CDate(dateValue)你的部分功能将简单地返回“DATEVALUE”作为一个Date。使用.Numberformat属性获取格式：

Function dateCheck(dateValue As Date) As Boolean
    If dateValue.NumberFormat <> "mm/dd/yyyy" Then
        MsgBox "Please use the mm/dd/yyyy  date format!"
        dateCheck = True
    End If
End Function

回答by Ralph

The problem is that your question is compounded. There are actually two steps involved to your question:

问题是你的问题是复杂的。您的问题实际上涉及两个步骤：

1. Is txtStartDateactually a valid date?
2. If it is a date, is it formatted correctly?

1. 是txtStartDate实际上是一个有效的日期？
2. 如果是日期，格式是否正确？

The naming of txtStartDateimplies that you are getting a date as a text (in a form). Yet, you are passing it to your function with the assumption that txtStartDateis actually a date. This becomes obvious because the function dateCheckexpects a date: Function dateCheck(dateValue As Date) As Boolean.

的命名txtStartDate意味着您以文本形式（以表格形式）获取日期。然而，您将它传递给您的函数，并假设txtStartDate实际上是一个日期。这变得很明显，因为该函数dateCheck需要一个日期：Function dateCheck(dateValue As Date) As Boolean。

So, here is a solution proposal to solve both at once:

所以，这里有一个解决方案可以同时解决这两个问题：

Sub tmpTest()

Dim txtStartDate As String

txtStartDate = "11/20/2015"

Debug.Print dateCheck(txtStartDate)

End Sub

---

---

Function dateCheck(dateValue As String) As Boolean

If IIf(IsDate(txtStartDate), Format(CDate(txtStartDate), "mm/dd/yyyy"), "") = dateValue Then dateCheck = True

End Function

Keep in mind that this is a very simplistic approach which will not work for international date formats. In fact, you might have to adjust it a bit as I do not have your date format. If you need something more sophisticated then you will have to write a bit more VBA code (including an extensive validation function).

请记住，这是一种非常简单的方法，不适用于国际日期格式。事实上，您可能需要稍微调整一下，因为我没有您的日期格式。如果您需要更复杂的东西，那么您将不得不编写更多的 VBA 代码（包括广泛的验证功能）。

回答by user3598756

Function dateCheck(dateStrng As String) As Boolean
Dim dateArr as Variant 

If IsDate(dateStrng) Then ' <~~ if it IS a date
   dateArr = Split(dateStrng,"/")
   If UBound(dateArr) = 2 Then '<~~ if it has three substrings separate by two "slashes"
      If CInt(dateArr(0)) < 13 Then  '<~~ if the 1st number is lower or equals the maximum possible month number
         If CInt(dateArr(0)) > 0 Then  '<~~ if the 1st number is higher or equals the mimimum possible month number
            If CInt(dateArr(1)) < 31 Then  '<~~ if the 2nd number is lower or equals the maximum possible day number
               If CInt(dateArr(1)) > 0 Then  '<~~ if the 2nd number is higher or equals the mimimum possible day number
                  If CInt(dateArr(2)) < 10000 Then dateCheck = CInt(dateArr(2)) > 999  '<~~ if the 3rd number is a 4 digit integer "year" number, then check returns True
               End If
            End If
         End If
      End If
   End If
End Function