Exporting Times (Durations) Initially Stored As Short Text As [hh]:mm (1 Viewer)

[Cark]

I have a column in my table where I am trying to collect a load of data for Delay Times which are durations in [hh]:mm format.

Sometimes the delay durations are over 100 hours long e.g 105:15 (105 hours 15 minutes) therefore I have had to input and store the data as text.

I am struggling to come up with a solution on how to export this data into Excel. I have created 2 extra columns to isolate the Delay Hours and Delay Minutes and have been able to convert them into numbers easily so could do some manipulation with these.

What I am wanting to do is to be able to export these into Excel already set up in the [hh]:mm format in Excel so that I can select (by dragging over multiple cells) and see the times automatically added up.

Currently what I am getting when I use the export I get the cells formatted (they say they are [hh]:mm format, however they still only give the count and not the sum when highlighted) and appearing with the numbers that I want, but the cells do not add up they only show a count when highlighted. I then have to highlight the column where the times are and do a find and replace where I replace ":" with ":", which sounds silly replacing : with itself, but this forces the cells to switch to the [hh]:mm format like I want.

The bit where I format the columns is:

.Range("N:N").NumberFormat = "hh:mm;@"

Which I have inside of the following code:

    Set objApp = CreateObject("Excel.Application")
    objApp.Visible = True
    Set wb = objApp.workbooks.Open(FileName, True, False)
    'select all worksheets & cells In turn
    For Each WS In wb.worksheets
    With WS
            .Cells.Font.Name = "Arial"
            lastrow = .Range("A1").currentregion.Rows.Count
            lastCol = .Range("A1").currentregion.Columns.Count
        .Columns("D").Font.Bold = True
        .Columns("D").Font.Italic = True
        .Range("N:N").NumberFormat = "hh:mm;@"
        .Rows(1).Font.Bold = True
        .Rows(1).Font.Italic = False
        .Rows(1).Interior.Color = RGB(200, 200, 200)
        .Rows(1).Font.Color = RGB(0, 0, 0)

    End With
    
    Next 'next worksheet
 objApp.sheets(1).Activate
    Set objApp = Nothing
    
End Sub

Is there a line / snippet of code which can replace my manual find + replace bit? Because once I have done the find and replace step, my export is perfectly set to how I want it. My issue is that I want to remove having to do this manually for each export.
If someone has a better solution to do this whole procedure, I would happily follow it if they could provide me with a "holding by the hand" solution.

 

[Cark]

Effectively the code I want to add should be something like this:

    Columns("H:H").Select
    Selection.Replace What:=":", Replacement:=":", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

How would I go about integrating this into my previous code?