How to convert milli seconds to hh:mm:ss AM/PM format in Excel macro

Help Activities
, ,
1

Hi,
I need to convert particular time column milli seconds to hh:mm:ss AM/PM(12 hr) format in excel.
we are having huge data in excel, need to run VBA macro code to achieve this. Could you please help on it.

Sample data:

Time
0.63045138888889
0.63045138888889
0.63045138888889
0.63045138888889
0.63045138888889
0.63045138888889
0.75921296296296

2

Hi, response from chat gpt lol; you can update the method to receive the inputs as needed

Sub ConvertTimeFormat()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim lastRow As Long
    
    ' Set the worksheet (change "Sheet1" to your sheet name if needed)
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Find the last row with data in the "Time" column (Assume column A)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Define the range (assume data starts from A2)
    Set rng = ws.Range("A2:A" & lastRow)
    
    ' Loop through each cell in the range
    For Each cell In rng
        If IsNumeric(cell.Value) Then
            ' Convert numeric value to time format
            cell.Value = Format(cell.Value, "hh:mm:ss AM/PM")
        End If
    Next cell
    
    ' Inform the user
    MsgBox "Conversion complete!", vbInformation
End Su