VBA to Convert Time Format and Filter Columns By Time

[dljjackson]

Macro to Convert Time Format and Filter Into Columns By Time

Hello,

I'm looking for some assistance in using vba to convert the format of time in a column from standard to military timing.

Background
In the linked document 'Activity Detail' the time I'm looking to be converting is in Column P 'Time Submitted'. The current VBA I use is in the attached file 'SOD Reporting'. I copy and paste activity detail into cell A1 of 'SOD Reporting' then hit unique contacts button to get my results. I received a lot of help with this but now I'm looking to expand it and will need some more assistance.

Next Steps
I would like to add the following columns based on the 'Time Submitted' and 'Form Name' columns in the 'Activity Detail document'

which require analysis of the data.
Knocks by 1PM
-Count of 'Form Name' up to 1pm
Knocks by 3pm
-Count of 'Form Name' up to 3pm
Knocks by 5pm
-Count of 'Form Name' up to 5pm
Knocks after 5pm
-Count of 'Form Name' after 5pm
Knocks by 7pm
-Count of 'Form Name' after 7pm
Knocks by after 7pm
-Count of 'Form Name' after 7pm

Here is my current code:

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Sub UniqueContactsReport()
    Dim cn As Object, rs As Object, i As Long, temp As String
    With CreateObject("Scripting.FileSystemObject")
        temp = ThisWorkbook.Path & "" & Replace(.GetTempName, "tmp", .GetExtensionName(ThisWorkbook.Name))
    End With
    ThisWorkbook.SaveCopyAs temp
    With Sheets("Activity Detail")
        .Cells.ClearContents
        Set cn = CreateObject("ADODB.Connection")
        Set rs = CreateObject("ADODB.Recordset")
        With cn
            .Provider = "Microsoft.Ace.OLEDB.12.0"
            .Properties("Extended Properties") = "Excel 12.0;HDR=Yes;IMEX=1;"
            .Open temp
        End With
        rs.Open "Select `Team`,`Supervisor`, `D2D Rep`,  Sum(`Sold`) As `Sold`, " & _
                "Sum(`Contact`) As `Unique Contacts` From `Activity Detail$` " & _
                "Where `D2D Rep` Is Not Null Group By `D2D Rep`, " & _
                "`Supervisor`, `Team` Order By `Team`, Sum(`Sold`), Sum(`Contact`)", cn
        For i = 0 To rs.Fields.Count - 1
            .Cells(1, i + 1).Value = rs.Fields(i).Name
        Next
        .[a2].CopyFromRecordset rs
 With .Cells(1).CurrentRegion
            .FormatConditions.Delete
            .FormatConditions.Add Type:=xlExpression, Formula1:="=and(isnumber($d1),$D1=1)"
            .FormatConditions(1).Interior.Color = 5296274
            .FormatConditions.Add Type:=xlExpression, Formula1:="=and(isnumber($d1),$D1>1)"
            .FormatConditions(2).Interior.Color = RGB(0, 153, 51)
        End With
        .Columns("A:B").ColumnWidth = 25
        .Columns("C").ColumnWidth = 20
        .Columns("D").ColumnWidth = 8
        .Columns("E").ColumnWidth = 17
        .Rows("2:200").RowHeight = 15
    End With
    Set cn = Nothing: Set rs = Nothing
    Kill temp
End Sub</code>

Any assistance would be greatly appreciated.

Links to documents:
https://drive.google.com/file/d/1zcH6Xqa4a7hakVNKb1nFjYPAODzU1VwT/view?usp=sharing
https://drive.google.com/file/d/165ub5BGPFz40aQVzdc6MrdD0nqjMYg9_/view?usp=sharing

This question is also posted on another site:
https://www.excelforum.com/excel-pr...-time-post4887895.html?highlight=#post4887895
Thank You​