Date -> numeric translation issue

[stanl]

Hope I state this correctly: I receive large csv downloads from a Predictive Dialer. After some initial modifications I am ready to further process the file (create a header row, and some tidying up.

I am stuck with one column of data that might present as:

0:08:32 formatted as custom h:mm:ss which is really is 12:08:32 AM

I need to have these values as numeric as fraction of an hour, so the value just cited is 0.142222. I can manually create a formula in the adjacent column (this is the last column) as celltoLeft/0.041666667 save the results as numeric, copy/replace over date values then delete the formula column. Or I can change the column to numeric, create another column as celltoleft * 24 and get the same value.

My question is can I iterate or the cell values, or recalculate the entire column as numeric w/out the new column/copy replace stuff?

[SamT]

working

[snb]

If it concerns column C :

[C2:C3000]=[if(C2:C3000="","",1*C2:C3000)]

Although I do not understand the formula you are using nor the desired result.
I only want to demonstrate the method you can use without a 'helper' column.

[SamT]

Sub test()
Dim Cel As Range

For Each Cel In Range("A1:A2")
  Cel.NumberFormat = "General"
  Cel = Cel.Value * 24
Next Cel

End Sub

[stanl]

Sub test()
Dim Cel As Range

For Each Cel In Range("A1:A2")
  Cel.NumberFormat = "General"
  Cel = Cel.Value * 24
Next Cel

End Sub

Thanks. Changed to

Cel.NumberFormat = "0.00000000"

and it worked perfectly.

[shrivallabha]

You can skip loop if you want to:

With Range("A1:A100")
    .NumberFormat = "0.00000000"
    .Value = Evaluate("=" & .Address & "*24")
End With