Excel VBA检查单元格是否包含日期 [英] Excel VBA check if cell contains date

问题描述

大家好，

我想检查单元格的范围，并希望只允许以mm / dd / yyyy格式输入的日期。我尝试应用excel数据验证，但如果我输入日期为1/1（未输入年份或1月1日），它也会例外。

I want to check range of cells and want to allow only dates in them entered in mm/dd/yyyy format. I tried applying excel data validation but it also excepts if i enter date as 1/1 (no year entered or 1 Jan)

我也试过下面的代码，但它也做了如果没有年份输入日期，如1/1或1月1日，则不会抛出错误。

I also tried below code but it also do not throw error if date in entered without year like 1/1 or 1 Jan

Private Sub Worksheet_Activate()
If Not IsDate(ActiveSheet.Range("A1").Value) And ActiveSheet.Range("A1").NumberFormat <> "m/d/yyyy" Then
MsgBox "Date is not valid"
End If

End Sub

我希望代码检查2件事。

I want code to check 2 things.

1. 单元格中的值必须为日期且
2. 日期必须为MM / DD / YYYY格式
3. 即使该单元格为空白而不是错误，也会弹出错误信息框。

谢谢，

Zaveri

推荐答案

日期值是数字，例如今天的日期值为42095（自1900年1月1日以来的天数）。

Date values are numbers, eg today's date as a date value is 42095 (days since 1/1/1900).

您的事件代码可以检查日期值是否在有效范围内，如果确定，则在必要时更正数字格式。根据您的需要调整以下内容

Your event code can check the date value is within a valid range, and if OK correct the numberformat if/as necessary. Adapt the following to your needs

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sAdd As String
Dim dtOldest As Date
Dim dtLatest As Date
Dim rng As Range, cel As Range
Static bExit As Boolean

    If bExit Then
        Exit Sub
    Else
        bExit = True
    End If

    On Error GoTo errExit
    saddr = "A1:A10"  ' << change
    dtOldest = #1/1/2000#    'm/d/y
    dtLatest = #12/31/2019#

    Set rng = Intersect(Range(saddr), Target)

    If Not rng Is Nothing Then
        bExit = True
        For Each cel In rng
            If IsDate(cel) Then
                If cel.Value < dtOldest Or cel.Value > dtLatest Then
                    MsgBox "Dates must be between " & _
                           Format(dtOldest, "mm\/dd\/yyyy") & " - " & _
                           Format(dtLatest, "mm\/dd\/yyyy")
                    cel.Clear
                ElseIf cel.NumberFormat <> "mm/dd/yyyy" Then
                    cel.NumberFormat = "mm\/dd\/yyyy"
                End If
            Else
                MsgBox cel.Address & " " & cel.Text & vbCr & " is not a valid date"
                cel.Clear
            End If
        Next

    End If

errExit:
    bExit = False
End Sub

将代码粘贴到相关的工作表模块中

Paste the code in the relevant worksheet module

这篇关于Excel VBA检查单元格是否包含日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！