Help with time formatting

[JEC8878]

Hi

Is there a way of changing times with AM and PM on them to just hh:mm
I have attached document

 

[Debaser]

If the dot is the normal time separator in your region, just replace 'am' and 'pm' with nothing using the Find/Replace dialog. If your region uses the colon, replace the dot with a colon first.

 

[Logit]

The following macro will process the cells to the preferred format :

Option Explicit

Sub ReformatTimes()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim columnsToFormat As Variant
    Dim col As Variant
    Dim cellValue As String
    Dim correctedValue As String

    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("ecm_formatted") ' Change "Sheet1" to your sheet name

    ' Define the columns to format
    columnsToFormat = Array("I", "J", "K", "L")

    ' Loop through each column
    For Each col In columnsToFormat
        ' Set the range for the column
        Set rng = ws.Range(col & "1:" & col & ws.Cells(ws.Rows.Count, col).End(xlUp).Row)

        ' Loop through each cell in the range
        For Each cell In rng
            ' Check if the cell is not empty
            If cell.Value <> "" Then
                cellValue = cell.Value

                ' Correct any non-standard time formats
                If InStr(cellValue, "/") > 0 Then
                    correctedValue = Replace(cellValue, "/", ":")
                    If IsDate(correctedValue) Then
                        ' Convert the corrected time to 24-hour format
                        cell.Value = Format(TimeValue(correctedValue), "hh:mm")
                    End If
                ElseIf IsDate(cellValue) Then
                    ' Convert the time to 24-hour format
                    cell.Value = Format(TimeValue(cellValue), "hh:mm")
                End If
            End If
        Next cell
    Next col
End Sub

 

[vletm]

JEC8878​

How do You get that data?
... could You get it more usable way?
You gotta check all Your data eg take care cell J9 manually 21/30pm.

 

[JEC8878]

If the dot is the normal time separator in your region, just replace 'am' and 'pm' with nothing using the Find/Replace dialog. If your region uses the colon, replace the dot with a colon first.

i tried this it didnt work but thank you

 

[vletm]

JEC8878​

How did You try it?
... I got it work