[SOLVED] Find Days Difference between two UK dates in vba userform

Find Days Difference between two UK dates in vba userform

These dates are produced by Trevor Eyre's calendar in USA format so I have converted to UK for my users. However I guess that is why I can't find date difference as no longer a number. Is there a way?

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

After several days realised sDate was repeated in TextBoxes 6 and7. At 77 my brain is addled. This now works fine. Thanks `Private Sub TextBox6_Enter() ' CALENDAR arrival date

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

sDate = Format(dateVariable, "dd/mm/yyyy") 'changes format from USA to UK TextBox6.value = sDate 'entry to textbox End Sub

Private Sub TextBox7_Enter() 'CALENDAR date leave

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

tDate = Format(dateVariable, "dd/mm/yyyy") 'changes format from USA to UK TextBox7.value = tDate

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

Solution

Use two module level variables, and you can reduce this significantly:

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