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

K

kokoanutt

New Member
Joined
May 17, 2024
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
@kokoanutt, welcome to the Forum.
Could you please provide some example data along with the expected results?
 
Upvote 0
Here is an example. Because the Broadcast Day begins at 6am and technically ends at 5:59:59am, the 2:33am line needs to be listed after midnight and it is currently listed before the 10am line.
 

Attachments

  • Screenshot 2024-05-17 174427.png
    Screenshot 2024-05-17 174427.png
    36.1 KB · Views: 19
Upvote 0
Do you only have 1 date in your data set?
If so, try this:
I use col F as temporary helper column.
The code will add 18 hours to each value in col B > put the result in col F > sort by col F > clear col F
VBA Code: 
Sub Sort_Hour_1()
Dim dt As Date
Dim i As Long, n As Long
Dim va
    n = Range("B" & Rows.Count).End(xlUp).Row
    va = Range("B1:B" & n)
    
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

'using col F as temporary helper column
Range("F1").Resize(UBound(va, 1), 1) = va
Range("A1").Resize(n, 6).Sort Key1:=Range("F1"), Order1:=xlAscending, Header:=xlNo
Range("F1").Resize(UBound(va, 1), 1).ClearContents

End Sub
Example:
Book1
ABCD
115:59:59 AM11
226:00:00 AM22
3311:30:00 AM33
448:30:00 PM44
Sheet4


Result:
Book1
ABCD
126:00:00 AM22
2311:30:00 AM33
348:30:00 PM44
415:59:59 AM11
Sheet4
 
Upvote 0
Thank you so much for your assistance @Akuini. This is all brand new to me.

Here is a more representative example. Ideally, I have already inserted a blank row in between the different Reference information and would also like to add something like a "Bottom Border" to separate the different dates.
1716012099139.png


Please let me know if you need more information.
 
Upvote 0
Could you post your example using the XL2BB tool?

OR

Could you please upload a sample workbook (without sensitive data) to a file-sharing site like Dropbox.com or Google Drive, and then share the link here? Also, ensure that the link is accessible to anyone.

so we can easily copy-paste your example to our sheet.
 
Upvote 0
Hit Time_TEST.xlsx
ABCDE
1DateHit TimeTicket No.ReferenceLength
205/18/202411:18:02 PM1242544MY TV - 10865530
305/18/202411:38:40 PM1242544MY TV - 10865530
4
505/17/202412:49:43 AM117597215
605/17/202407:56:58 AM117597215
705/17/202404:40:57 PM117597215
805/18/202412:32:45 PM117597415
9
1005/17/202412:08:41 AM1239622Sure - 5994615
1105/17/202412:28:00 AM1239622Sure - 5994615
1205/17/202412:37:09 AM1239622Sure - 5994615
1305/17/202412:57:26 AM1239622Sure - 5994615
1405/17/202401:08:33 AM1239622Sure - 5994615
1505/17/202401:38:51 AM1239622Sure - 5994615
1605/17/202401:47:51 AM1239622Sure - 5994615
1705/17/202411:50:37 AM1239622Sure - 5994615
1805/17/202412:50:05 PM1239622Sure - 5994615
1905/17/202401:43:39 PM1239622Sure - 5994615
2005/17/202402:47:28 PM1239622Sure - 5994615
2105/17/202406:20:05 PM1239622Sure - 5994615
2205/17/202406:59:11 PM1239622Sure - 5994615
2305/17/202409:10:24 PM1239622Sure - 5994615
2405/17/202411:08:30 PM1239622Sure - 5994615
2505/17/202411:27:55 PM1239622Sure - 5994615
2605/17/202411:49:10 PM1239622Sure - 5994615
2705/18/202408:27:36 AM1239622Sure - 5994615
2805/18/202409:55:29 AM1239622Sure - 5994615
2905/18/202409:02:07 PM1239622Sure - 5994615
30
3105/17/202409:25:34 AM1194060LIN - 6039760
3205/17/202412:34:27 PM1194060LIN - 6039760
3305/17/202408:09:36 PM1194060LIN - 6039760
3405/17/202408:57:41 PM1194060LIN - 6039760
3505/17/202410:56:58 PM1194060LIN - 6039760
36
3705/17/202403:27:31 PM1215754MAV - 10408945
3805/17/202403:38:41 PM1215754MAV - 10408945
3905/18/202409:10:51 AM1215754MAV - 10408945
4005/18/202410:33:20 AM1215754MAV - 10408945
41
4205/17/202404:49:13 PM1167590GEN - 1222930
4305/18/202402:10:59 PM1167590GEN - 1222930
4405/18/202402:40:41 PM1167590GEN - 1222930
4505/18/202403:35:55 PM1167590GEN - 1222930
4605/18/202406:19:25 PM1221764GEN - 1222930
4705/18/202407:57:50 PM1221764GEN - 1222930
48
4905/17/202408:09:52 AM123492715
5005/18/202412:31:28 AM123492715
5105/18/202410:34:35 AM123492715
5205/18/202404:56:07 PM123492715
5305/18/202405:22:34 PM123492715
5405/18/202406:35:07 PM123492715
5505/18/202407:47:17 PM123492715
5605/18/202408:12:53 PM123492715
5705/18/202408:50:16 PM123492715
5805/18/202409:15:59 PM123492715
5905/18/202410:19:09 PM123492715
6005/18/202411:03:42 PM123492715
6105/18/202411:54:18 PM123492715
6205/17/202409:53:29 AM123492730
6305/18/202404:18:59 PM123492730
Hit Times
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:E440Expression=CELL("contents", INDIRECT(ADDRESS(ROW(), 1))) <> 0textNO
 
Upvote 0
Try this one:
VBA Code: 
Sub Sort_Hour_2()
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
    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
    
    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
Result:
Book1
ABCDE
1DateHit TimeTicket No.ReferenceLength
205/18/202411:18:02 PM1242544MY TV - 10865530
305/18/202411:38:40 PM1242544MY TV - 10865530
4
505/17/20247:56:58 AM117597215
605/17/20244:40:57 PM117597215
705/17/202412:49:43 AM117597215
805/18/202412:32:45 PM117597415
9
1005/17/202411:50:37 AM1239622Sure - 5994615
1105/17/202412:50:05 PM1239622Sure - 5994615
1205/17/20241:43:39 PM1239622Sure - 5994615
1305/17/20242:47:28 PM1239622Sure - 5994615
1405/17/20246:20:05 PM1239622Sure - 5994615
1505/17/20246:59:11 PM1239622Sure - 5994615
1605/17/20249:10:24 PM1239622Sure - 5994615
1705/17/202411:08:30 PM1239622Sure - 5994615
1805/17/202411:27:55 PM1239622Sure - 5994615
1905/17/202411:49:10 PM1239622Sure - 5994615
2005/17/202412:08:41 AM1239622Sure - 5994615
2105/17/202412:28:00 AM1239622Sure - 5994615
2205/17/202412:37:09 AM1239622Sure - 5994615
2305/17/202412:57:26 AM1239622Sure - 5994615
2405/17/20241:08:33 AM1239622Sure - 5994615
2505/17/20241:38:51 AM1239622Sure - 5994615
2605/17/20241:47:51 AM1239622Sure - 5994615
2705/18/20248:27:36 AM1239622Sure - 5994615
2805/18/20249:55:29 AM1239622Sure - 5994615
2905/18/20249:02:07 PM1239622Sure - 5994615
30
Sheet2


However, I'm confused about how you write the time format. The AM/PM format follows the 12-hour clock system, so it shouldn't be used for times greater than 12 hours. However, in your dataset, there are many instances like that, such as 12:49:43 AM in cell B7, while in the formula bar it says 00:49:43, so shouldn't it be written as 00:49:43 AM?
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Delete rows before a certain time
2 3
Replies
26
Views
900
Joe4
Joe4
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
423
dshtesy
D
K
  • Solved
help with formatting a cell (for TIME, custom format) that retrieves its value from another cell...
Replies
2
Views
371
kbishop94
K
wizau
  • Solved
Nested IF statements, or a better way?
Replies
6
Views
424
wizau
wizau
T
  • Solved
Looking for VBA Code to Conditionally Format Time Tracker on Entry
Replies
3
Views
199
Tara_A_Data
T

Forum statistics

Threads
1,224,823
Messages
6,181,180
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top