Is there a way to sort time according to a Broadcast Day (6am-6am)

[kokoanutt]

Is there any way to create a Macro to sort a column with Hit Times according to a Broadcast Day of 6am-6am? I have searched everywhere and have not been able to find anything. The way that Excel currently sort information is from Smallest to Largest. However, I want something with a Hit Time of 06:30:00 to be listed before something with a Hit Time of 02:15:30, based on the Broadcast Day. Hopefully this makes sense to someone and there is genius out there that can assist. Thank You!

 

[kokoanutt]

Excellent question on the display time @Akuini . I'm not sure who created this logic and why it is displayed in the 12-hour format. In other modules within this ecosystem, the time is displayed in the 24-hour format. Doesn't make any sense.

What does this statement mean?
"For i = 1 To UBound (va, 1)"

 

[Akuini]

Did you try my code? Does it work as expected?

What does this statement mean?
"For i = 1 To UBound (va, 1)"

Variable va holds values from column B, and we need to loop through those values in va and change them as needed.
"'For i = 1 To UBound(va, 1)" is used to loop through those values starting from the first value until the last one.

 

[kokoanutt]

Did you try my code? Does it work as expected?

Variable va holds values from column B, and we need to loop through those values in va and change them as needed.
"'For i = 1 To UBound(va, 1)" is used to loop through those values starting from the first value until the last one.

I did try the code, but it got stuck at "For i = 1 To UBound(va, 1)" , which was why I was asking. I was going to try and figure it out.

 

[Akuini]

I did try the code, but it got stuck at "For i = 1 To UBound(va, 1)"

Are you saying the code got stuck when using the example in post #9 or in your actual workbook?

 

[kokoanutt]

Are you saying the code got stuck when using the example in post #9 or in your actual workbook?

In my actual Workbook

 

[Akuini]

In my actual Workbook

Using the code in post #10, if any grouped data consists of only 1 row, the code will fail.
So, try this one instead:

Sub Sort_Hour_3()
Dim dt As Date
Dim i As Long, n As Long
Dim va
Dim c As Range, f As Range

Application.ScreenUpdating = False
'"A2" means start at A2
Set f = Range("A2", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeConstants)

For Each c In f.Areas
    va = c.Offset(, 1) 'get col B
    If IsArray(va) Then
        For i = 1 To UBound(va, 1)
            dt = CDate(va(i, 1))
            dt = DateAdd("h", 18, dt)  'adding 18 hours
            va(i, 1) = Format(dt, "hh:nn:ss AM/PM")
        Next
    End If
    c.Offset(, 5) = va 'fill temporary helper column F with time+18 hours
    With c.Resize(, 6) 'expand to col A:F
        'sort ascending by col 1, then  sort ascending by col 6
        .Sort Key1:=.Columns(1), Order1:=xlAscending, Key2:=.Columns(6), Order2:=xlAscending, Header:=xlNo
        c.Offset(, 5).ClearContents
    End With
Next
   
Application.ScreenUpdating = True
End Sub

 

[kokoanutt]

Using the code in post #10, if any grouped data consists of only 1 row, the code will fail.
So, try this one instead:

Sub Sort_Hour_3()
Dim dt As Date
Dim i As Long, n As Long
Dim va
Dim c As Range, f As Range

Application.ScreenUpdating = False
'"A2" means start at A2
Set f = Range("A2", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeConstants)

For Each c In f.Areas
    va = c.Offset(, 1) 'get col B
    If IsArray(va) Then
        For i = 1 To UBound(va, 1)
            dt = CDate(va(i, 1))
            dt = DateAdd("h", 18, dt)  'adding 18 hours
            va(i, 1) = Format(dt, "hh:nn:ss AM/PM")
        Next
    End If
    c.Offset(, 5) = va 'fill temporary helper column F with time+18 hours
    With c.Resize(, 6) 'expand to col A:F
        'sort ascending by col 1, then  sort ascending by col 6
        .Sort Key1:=.Columns(1), Order1:=xlAscending, Key2:=.Columns(6), Order2:=xlAscending, Header:=xlNo
        c.Offset(, 5).ClearContents
    End With
Next
  
Application.ScreenUpdating = True
End Sub

I cannot thank you enough!!!!

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.

 

[kokoanutt]

You're welcome, glad to help & thanks for the feedback.

My pleasure! It is the least that I can do for you. You've been a lifesaver for me.