Converting text to date works on one system but not on others

user_16959411 0
Viewed 81

I created a macro Excel file for sorting some data and to do some Vlookup function with the same data. The macro is working perfectly in my system. The same macro I shared with my colleague he is also using the same dataset but the final result are not correct, or not matching with my results.

How we can fix this please?

The dataset is quite large. Nearly 200k lines are there.

They are not getting any errors. The macro is running the full code without any errors in their system as well, but the final report is not matching or not correct.

The mismatch is coming only for the date area. There is column with dates mm/dd/yyyy formated as text. I used to convert this text formated date into date then I have to remove all the future dates from the column.

This function alone not working properly for others. Rest all the part is clear. But this is working perfectly in my system.

Below are the Code i used to covert the text date into numeric date and removing the future date then change the available date into old format

    Private Sub TermDateRemove()

Dim CellValue, OldFrmtDate As String
Dim y, Tday As Date
Dim EmpTerDate As Range
Dim EmpTerDatePos As Integer
Sheets("Emp Data").Activate
LR = Worksheets("Emp Data").UsedRange.Rows.count
LC = Worksheets("Emp Data").UsedRange.Columns.count

If ActiveSheet.AutoFilterMode = True Then
    ActiveSheet.AutoFilterMode = False
End If

Set EmpTerDate = Sheets("Emp Data").Range("1:1").Find("Employee Termination Date", , xlValues, xlWhole, , , False, , False)
    EmpTerDatePos = EmpTerDate.Column
    
txt2clm (EmpTerDatePos) 'Calling text to column function

Tday = Format(Date, "dd/mm/yy")
With Worksheets("Emp Data")
    .Range("1:1").AutoFilter Field:=EmpTerDatePos, Criteria1:=">" & Tday, Operator:=xlFilterValues
End With
If ActiveSheet.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).count > 1 Then
With Worksheets("Emp Data")
    .Range(.Cells(2, EmpTerDatePos), .Cells(LR, EmpTerDatePos)).SpecialCells(xlCellTypeVisible).Cells.Value = ""
End With
End If
If ActiveSheet.AutoFilterMode = True Then
    ActiveSheet.AutoFilterMode = False
End If

Columns(EmpTerDatePos).NumberFormat = "@"

For Each cell In Sheets("EMP DATA").UsedRange.Columns(EmpTerDatePos).Cells
    CellValue = cell.Value
    If CellValue = "Employee Termination Date" Then
     'do nothing
    ElseIf CellValue <> "" And CellValue <> "Employee Termination Date" Then
        CVtoDate = Format(CellValue, "mm/dd/yyyy")
        OldFrmtDate = Replace(CVtoDate, "-", "/")
        cell.Value = OldFrmtDate
    End If
Next

Workbooks("Employee Data").Save
End Sub

-----Function-----

    Function txt2clm(cc As Long) As Long

'Text to column for changing the date into numeric format
    Columns(cc).Select
    Selection.TextToColumns Destination:=Range(ActiveSheet.Cells(1, cc), ActiveSheet.Cells(1, cc)), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 3), TrailingMinusNumbers:=True
End Function
vba excel
1 Answers

Convert the String (looking like a date) dd/mm/yy into a real/numeric date by splitting it into parts by / and using DateSerial.

Dim Cell As Range
For Each cell In Sheets("Emp Data").UsedRange.Columns(EmpTerDatePos).Cells
    Dim CellValue As String
    CellValue = cell.Value

    If CellValue <> vbNullString And CellValue <> "Employee Termination Date" Then  
        Dim SplitDate As String
        SplitDate = Split(CellValue, "/")         

        Dim DayFromDate As Integer
        DayFromDate = CInt(SplitDate(1))

        Dim MonthFromDate As Integer
        MonthFromDate = CInt(SplitDate(0))  

        Dim YearFromDate As Integer
        YearFromDate = CInt(SplitDate(2))  
   
        Dim NumDate As Date
        NumDate = DateSerial(YearFromDate, MonthFromDate, DayFromDate)         
        cell.Value = NumDate     
    End If 
Next

Also make sure you don't use a String as filter criteria, this might fail on other computers again. Never use Strings for date.

Even you Dim Tday As Date the Format function returns a String that you then implicitly cast into a numeric Date variable if you do Tday = Format(Date, "dd/mm/yy"). That means Excel has to guess if the String that comes out of the Format function is dd/mm/yy or mm/dd/yy. Even you specified that, VBA is not smart enough to use that information for interpretation of that string.

So what you do instead is:

Dim Tday As Date
Tday = Date

With Worksheets("Emp Data")
    .Range("1:1").AutoFilter Field:=EmpTerDatePos, Criteria1:=">" & CDbl(Tday), Operator:=xlFilterValues
End With

Here we convert the date Tday into a Double using CDbl(Tday). All numeric dates/times are represented by a number of the type Double. Therefore we use a Double as criteria. This is much more reliable as your code and will work on any county localization.

Last but not least try to avoid ActiveSheet and an explicit reference like Worksheets("Emp Data") so the code even works when your sheet is not active.

user_32196130
Related