VBA Excel: comparison of dates under different locale settings

user_9726164 0
Viewed 70

I have a column in "dd-mm-yy hh:mm" format that formed as a result of some action on UserForm:

Dim ws as Worksheet
Set ws = Worksheets("Logs")
With ws
  For i = 1 to Me.ListBox1.ListCount - 1
    .Cells(lRow + 1 + i, 10).Value = CDate(VBA.Format(Me.ListBox1.List(i), "dd-mm-yy hh:mm"))
Next i
End With

excel column with datetime

I save the column to Variant variable to use later (to be used multiple times):

Dim arrTimeD As Variant
arrTimeD = Application.Transpose(.Range(TCL & "2:" & TCL & lRow).Value)

The locale date settings are European: "dd-mmm-yyyy"

The spreadsheet are used by different users, some have "dd-mmm" setting, others "mm-dd" etc.

I need to compare the dates in several uses. For, e.g.

Dim bDate as Date
bDate = CDate(VBA.Format(Me.lblCheckin.Caption,"dd-mm-yyyy"))
Do While CDate(arrTimeD(bIndex)) < bDate
      If bIndex = lRow - 1 Then Exit Do
      bIndex = bIndex + 1            
Loop

When the user with US locale ("mm-dd") uses the spreadsheet, CDate(arrTimeD(bIndex)) throws error. CDate(VBA.Format(arrTimeD(bIndex))) and CDate(DateValue(arrTimeD(bIndex)) didn't help. What is the best way to do it? Is it possible to set workbook's own date setting regardless of OS's? Or I need to convert variant to string then concatenate?

date-conversion vba excel type-conversion date
2 Answers

The date string should be converted into a numeric date value.

Function DDMMYYYFormatToDateTimeValue(DateString As String) As Date
    Dim Parts() As String
    Parts = Split(DateString, "-")
    DDMMYYYFormatToDateTimeValue = CDate(Parts(1) & "/" & Parts(0) & "/" & Parts(2))
End Function

Usage

Private Sub UserForm_Initialize()
    Dim n As Long
    
    For n = 1 To 100
        ListBox1.AddItem Format(Date + n / 24, "MM-DD-YY HH:MM")
    Next
End Sub

Public Function ListBoxDateValues()
    Dim Data As Variant
    ReDim Data(1 To Me.ListBox1.ListCount, 1 To 1)
    
    Dim DateString As String
    
    Dim r As Long
    For r = 1 To Me.ListBox1.ListCount
        DateString = Me.ListBox1.List(r - 1)
        Data(r, 1) = DDMMYYYFormatToDateTimeValue(DateString)
    Next
    ListBoxDateValues = Data
End Function

Public Function wsLogs() As Worksheet
    Set wsLogs = ThisWorkbook.Sheets("Logs")
End Function

Function DDMMYYYFormatToDateTimeValue(DateString As String) As Date
    Dim Parts() As String
    Parts = Split(DateString, "-")
    DDMMYYYFormatToDateTimeValue = CDate(Parts(1) & "/" & Parts(0) & "/" & Parts(2))
End Function
user_99127140

First, true date values carry no format, so convert your text dates from the listbox directly to true date values:

.Cells(lRow + 1 + i, 10).Value = CDate(Me.ListBox1.List(i))

These you can apply the format you prefer for display.

The comparison is now straight:

Dim bDate As Date

bDate = CDate(Me.lblCheckin.Caption)
Do While arrTimeD(bIndex) < bDate
    If bIndex = lRow - 1 Then 
        Exit Do
    Else
        bIndex = bIndex + 1
    End If
Loop
user_35272970
Related