时间戳

首页 > 解决方案 > Excel 日期选择器日期格式

excel - Excel 日期选择器日期格式

问题描述

我遇到了一个问题，Excel 中的日期选择器似乎正在使用我正在搜索的日期的美国版本。

我正在尝试检查是否存在工作表，其中某个单元格范围内的日期存在于 DTpicker1 和 DTpicker2 的参数中。

谁能告诉我如何强制代码以英国格式检查日期？

Private Sub CommandButton1_Click()
Dim s As Worksheet, wb As Workbook
UserForm1.Hide
For Each s In Worksheets
    If CBool(Application.CountIfs(s.Range("E11:E37"), ">" & 
    Format(DTPicker1.Value, "dd/mm/yy"), _ s.Range("E11:E37"), "<" & 
Format(DTPicker2.Value, "dd/mm/yy"))) Then
        If wb Is Nothing Then
            s.Copy
            Set wb = ActiveWorkbook
        Else
            s.Copy after:=wb.Worksheets(wb.Worksheets.Count)
        End If
    End If
Next s

If wb Is Nothing Then
    MsgBox ("No Records Found")
Else
    wb.SaveAs Filename:="Technicians - Batch Record Report" & Format(Date, 
"ddmmyyyy"), _
          FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End If
End Sub

感谢@Jeeped，他让我走到了这一步！

更新：代码通过反转日期格式工作：

Private Sub CommandButton1_Click()
Dim s As Worksheet, wb As Workbook
UserForm1.Hide
For Each s In Worksheets
    If CBool(Application.CountIfs(s.Range("E11:E37"), ">" & 
    Format(DTPicker1.Value, "mm/dd/yy"), _ s.Range("E11:E37"), "<" & 
Format(DTPicker2.Value, "mm/dd/yy"))) Then
        If wb Is Nothing Then
            s.Copy
            Set wb = ActiveWorkbook
        Else
            s.Copy after:=wb.Worksheets(wb.Worksheets.Count)
        End If
    End If
Next s

If wb Is Nothing Then
    MsgBox ("No Records Found")
Else
    wb.SaveAs Filename:="Technicians - Batch Record Report" & Format(Date, 
"ddmmyyyy"), _
          FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End If
End Sub

标签： exceldatepickervba

解决方案

您可以手动强制检查

If dateValue.NumberFormat <> "mm/dd/yyyy" Then     '<-- provide the date format to validate with

推荐阅读