Time is endless

[Wil Moosa]

I’m working with a worksheet with plenty of time entries. In fact most of the data is imported from a secondary programme. As most of the imported data fits well in the format of the cells of the worksheet ([h]:mm) I have trouble with one column.

While most of the imported data uses the format h:mm there is one column that uses a decimal format. As a result the calculation within the worksheet is not correct ánd I get an error message as [h]:mm  19:60 does not excist.

The question is: using the cell format [h]:mm and adding from a secondary source decimal data to cells in one column, what vba code could I use to adjust the data and reformat the column to get the correct data?

 

[Andrew Poulsom]

Select the cells and run this:

Sub Test()
    Dim Cell As Range
    For Each Cell In Selection
        Cell.Value = WorksheetFunction.Substitute(Cell.Text, ":", ".") / 24
    Next Cell
End Sub

 

[Wil Moosa]

Andrew, it works great on a test workbook. Nice compact code.

I try tomorrow to get it working in the whole process of my project; importing the html and conversion of the data. I let you know -for the record- if it worked.

Thank you.