Using TRUNC of FIX function in to extract date part

[nhandal]

Hello,

I have a sheet that includes a list of datetime values in column B, I am using this code to loop through all rows to replace the number in each cell with only the date part of the number using the FIX function, but when I run the code VB returns an error "Object doesn't support this property or method. I can't figure out what is the problem. I appreciate someone's help.

Sub ConvertDateValues()

    Dim lRow As Long
    Dim lLastRow As Long
    Dim vCellDateNumber As Variant
        
    lLastRow = Worksheets("Sheet1").UsedRange.Rows.Count
    
    For lRow = lLastRow To 2 Step -1
            
        vCellDateNumber = Cells(lRow, 2).Value
        Range(Cells(lRow, 2)).Value = Application.WorksheetFunction.Fix(vCellDateNumber)

    Next lRow

End Sub

 

[Fluff]

There is no FIX function in Excel.
Do you want the dates to be left as dates, rather than being converted to text?

 

[nhandal]

There is no FIX function in Excel.
Do you want the dates to be left as dates, rather than being converted to text?

I initially wanted to use the TRUNC function but it did not work in VBA, I saw posts on some websites that suggest using the FIX function in VBA.
I want to keep the cell values as numbers, because I want to use them as dates but I only want to remove the time part in the number.

 

[Joe4]

"FIX" is a VBA function, not an Excel one.

So instead of referencing it like this:

... = Application.WorksheetFunction.Fix(vCellDateNumber)

you would just use:

... = Fix(vCellDateNumber)

 

[Fluff]

Another option is

Sub nhandal()
   With Range("B2", Range("B" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(@="""","""",int(@))", "@", .Address))
   End With
End Sub

 

[nhandal]

"FIX" is a VBA function, not an Excel one.

So instead of referencing it like this:

... = Application.WorksheetFunction.Fix(vCellDateNumber)

you would just use:

... = Fix(vCellDateNumber)

Thanks, now I get a new error message that says "Type mismatch"

 

[Joe4]

Thanks, now I get a new error message that says "Type mismatch"

It probably means you are trying to apply the FIX function to a non-numeric entry (text or an error, etc).

 

[nhandal]

Another option is

Sub nhandal()
   With Range("B2", Range("B" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(@="""","""",int(@))", "@", .Address))
   End With
End Sub

Thank you very much, this code works and it is much faster than the loop that used. I appreciate your help.

Just out of curiosity, do you know what is wrong in the code that I used?

 

[nhandal]

It probably means you are trying to apply the FIX function to a non-numeric entry (text or an error, etc).

How can this be? The values in this column are numbers not text.

 

[Joe4]

How can this be? The values in this column are numbers not text.

Without having access to your data, I really cannot say. It may be picking up something you aren't expecting.

BTW, if all the numbers are numbers, why have you declared the variable as Variant?

Dim vCellDateNumber As Variant

That seems to suggest that ANYTHING is allowed, and not just numbers.