主页 / coding / 问题 / 79582871
Accepted
nickC
Asked: 2025-04-20 06:26:57 +0800 CST

在vba用户表单中查找两个英国日期之间的天数差异

  • 772

这些日期是根据 Trevor Eyre 的美国格式日历生成的，所以我已将其转换为英国格式供用户使用。不过，我猜这就是为什么我找不到日期差值的原因，因为它不再是数字。有什么办法吗？

Private Sub TextBox6_Enter()
'CALENDAR arrival date
    Dim dateVariable As Date
    dateVariable = CalendarForm.GetDate
    Dim sDate As String 'changes format from USA for UK users
    sDate = Format(dateVariable, "dd-mmm-yy")
    TextBox6.value = sDate
End Sub
Private Sub TextBox7_Enter()
'CALENDAR date leave
    Dim dateVariable As Date
    dateVariable = CalendarForm.GetDate
    Dim sDate As String
'changes format from USA to UK
    sDate = Format(dateVariable, "dd-mmm-yy")
    TextBox7.value = sDate
End Sub
  Private Sub TextBox20_change()
'Number of NIGHTS
     TextBox20.value = DateDiff("d", DateValue(TextBox6.value), TextBox7.value)
End Sub

几天后，我意识到 sDate 在 TextBox 6 和 TextBox 7 中重复了。77 的时候我的脑子都糊涂了。现在一切正常了。谢谢 `Private Sub TextBox6_Enter()' CALENDAR 到达日期

Dim dateVariable As Date
dateVariable = CalendarForm.GetDate
TextBox6.value = dateVariable

sDate = Format(dateVariable, "dd/mm/yyyy") '将格式从美国更改为英国 TextBox6.value = sDate '输入文本框 End Sub

Private Sub TextBox7_Enter() '日历日期休假

Dim dateVariable As Date
dateVariable = CalendarForm.GetDate
TextBox7.value = dateVariable

tDate = Format(dateVariable, "dd/mm/yyyy") '将格式从美国更改为英国 TextBox7.value = tDate

TextBox20.Text = DateDiff("d", TextBox6.value, TextBox7.value) End Sub```

vba

3 个回答

  1. 当然，只要确保你比较的是同类数据，并且两个日期的格式相同即可。例如：

    Sub CompareDates()

    Dim sUS   As String
    Dim sUK   As String
    Dim dtUS  As Date
    Dim dtUK  As Date

    sUS = "12/1/2025"
    sUK = "2/12/2025"
    
    parts = Split(sUS, "/")
    dtUS = DateSerial( _
        CInt(parts(2)), _
        CInt(parts(0)), _
        CInt(parts(1)))

    parts = Split(sUK, "/")
    dtUK = DateSerial( _
        CInt(parts(2)), _
        CInt(parts(1)), _
        CInt(parts(0)))

     Debug.Print DateDiff("d", dtUS, dtUK)

End Sub
    • 0

  2. 将特定的“字符串日期”转换为日期

    • 通过使用一些辅助函数，您可以执行以下操作：

      TextBox6.Value = DateToUKString(CalendarForm.GetDate)
TextBox7.Value = DateToUKString(CalendarForm.GetDate)
TextBox20.Value = DateDiff( _
    "d", UKStringToDate(TextBox6.Value), UKStringToDate(TextBox7.Value))

    帮助

    Function DateToUKString(ByVal InputDate As Date) As String
    DateToUKString = VBA.Format(InputDate, "dd-mmm-yy")
End Function

    Function UKStringToDate(ByVal UKString As String) As Date
    Dim Parts() As String: Parts = VBA.Split(UKString, "-")
    On Error Resume Next
        UKStringToDate = VBA.DateSerial( _
            20 & Parts(2), GetMonthNumber(Parts(1)), Parts(0))
    On Error GoTo 0
End Function

    Function GetMonthNumber(ByVal Mmm As String) As Long
    Dim EngMonths() As Variant: EngMonths = VBA.Array( _
        "jan", "feb", "mar", "apr", "may", "jun", _
        "jul", "aug", "sep", "oct", "nov", "dec")
    Dim lcMonth As String: lcMonth = VBA.LCase(Mmm)
    Dim EngMonth As Variant, i As Long
    For Each EngMonth In EngMonths
        i = i + 1
        If EngMonth = lcMonth Then GetMonthNumber = i: Exit Function
    Next EngMonth
End Function

    测试

    Sub Test()
    
    Const str1 As String = "12-Feb-24"
    Const str2 As String = "17-May-24"
    
    Debug.Print DateDiff("d", str1, str2)
    Debug.Print DateToUKString(Date)
    Debug.Print DateToUKString(Date + 2100)
    Debug.Print UKStringToDate("") = 0

End Sub

    结果

     95 
20-Apr-25
19-Jan-31
True
    • 0
  3. Best Answer

    使用两个模块级变量，可以显著减少这种情况：

    Option Explicit

Private ArrivalDate As Date
Private LeaveDate   As Date

Private Sub TextBox6_Enter()

    ' CALENDAR arrival date.

    ArrivalDate = CalendarForm.GetDate
    TextBox6.Value = Format(ArrivalDate, "dd-mmm-yy")
    
End Sub

Private Sub TextBox7_Enter()

    ' CALENDAR date leave.
    
    LeaveDate = CalendarForm.GetDate
    TextBox7.Value = Format(LeaveDate, "dd-mmm-yy")
    
End Sub
  
Private Sub TextBox20_change()
    
    ' Number of NIGHTS.
    
    TextBox20.Value = DateDiff("d", ArrivalDate, LeaveDate)
    
End Sub
    • 0

相关问题