PDA

View Full Version : Date -> numeric translation issue



stanl
07-28-2013, 11:17 AM
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
07-28-2013, 12:38 PM
working

snb
07-28-2013, 12:45 PM
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
07-28-2013, 01:14 PM
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
07-29-2013, 07:04 AM
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
07-29-2013, 10:37 AM
You can skip loop if you want to:

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