Sorting spreadsheet by time

S

systemdude

New Member
Joined
Apr 9, 2015
Messages
6
Hi

I have a spreadsheet that contains among other columns, 2 columns. They are start time and end time. Their format is 7/1/2019 2:17:11 AM for example. Of all the start times, I want to ONLY retain the information where the start time is */*/2019 21:00:00 PM and the end time is */*/2019 22:00:00 PM.

How would I go about doing this?

Many thanks.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
Hello,

Your description is not totally clear.

My understanding is that you want to delete the rows where the start time isn't 21:00 in 2019 and the end time isn't 22:00 in 2019.

Code: 
Sub keep_2019_2100_2200()
    For MY_ROWS = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
        If Year(Range("A" & MY_ROWS).Value) <> 2019 Or Hour(Range("A" & MY_ROWS)) <> 21 Or _
        Year(Range("B" & MY_ROWS).Value) <> 2019 Or Hour(Range("B" & MY_ROWS)) <> 22 Then
            Rows(MY_ROWS).Delete
        End If
    Next MY_ROWS
End Sub
 
Upvote 0
Hello and many thanks. You are right, I was not clear. In addition to how you have interpreted it, I also want to retain rows where the start and end times fall in the same hour, that is, in 2100 hours or 2200 hours. So I tried to modify your code as follows - is this right?


Sub keep_2019_2100_2200()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For MY_ROWS = Range("F" & Rows.Count).End(xlUp).Row To 2 Step -1
If Year(Range("F" & MY_ROWS).Value) <> 2019 Or Hour(Range("F" & MY_ROWS)) <> 21 Or Hour(Range("F" & MY_ROWS)) <> 22 Or _
Hour(Range("G" & MY_ROWS)) <> 21 Or Hour(Range("G" & MY_ROWS)) <> 22 Or _
Year(Range("G" & MY_ROWS).Value) <> 2019 Then
Rows(MY_ROWS).Delete
End If
Next MY_ROWS
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub




==========================================
Your description is not totally clear.

My understanding is that you want to delete the rows where the start time isn't 21:00 in 2019 and the end time isn't 22:00 in 2019.

Code: 
Sub keep_2019_2100_2200()
    For MY_ROWS = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
        If Year(Range("A" & MY_ROWS).Value) <> 2019 Or Hour(Range("A" & MY_ROWS)) <> 21 Or _
        Year(Range("B" & MY_ROWS).Value) <> 2019 Or Hour(Range("B" & MY_ROWS)) <> 22 Then
            Rows(MY_ROWS).Delete
        End If
    Next MY_ROWS
End Sub
[/QUOTE]
 
Upvote 0
Hello,

doesn't my original code not do that?

If i start with

01/04/2019 20:00 01/04/2019 21:00
01/04/2019 21:00 01/04/2019 22:00
01/04/2019 21:01 01/04/2019 22:01
01/04/2019 21:02 01/04/2019 22:02
01/04/2019 21:03 01/04/2019 22:03
01/04/2019 21:04 01/04/2019 22:04
01/04/2019 21:05 01/04/2019 22:05
01/04/2019 21:06 01/04/2019 22:06
01/04/2019 21:07 01/04/2019 22:07
01/04/2019 21:08 01/04/2019 22:08
01/04/2019 22:00 01/04/2019 23:00

then run the code, I end up with

01/04/2019 21:00 01/04/2019 22:00
01/04/2019 21:01 01/04/2019 22:01
01/04/2019 21:02 01/04/2019 22:02
01/04/2019 21:03 01/04/2019 22:03
01/04/2019 21:04 01/04/2019 22:04
01/04/2019 21:05 01/04/2019 22:05
01/04/2019 21:06 01/04/2019 22:06
01/04/2019 21:07 01/04/2019 22:07
01/04/2019 21:08 01/04/2019 22:08

perhaps you can post a sample of data, with a before and after.
 
Upvote 0
Before:

[TABLE="width: 988"]
<colgroup><col><col span="2"><col><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]GATN[/TD]
[TD]UNK[/TD]
[TD]Radarsat-2[/TD]
[TD]60272[/TD]
[TD]X[/TD]
[TD]7/1/2019 20:45[/TD]
[TD]7/1/2019 20:53[/TD]
[TD]Success[/TD]
[TD]UNK[/TD]
[TD]final[/TD]
[/TR]
[TR]
[TD]ICAN[/TD]
[TD]UNK[/TD]
[TD]Radarsat-2[/TD]
[TD]60272[/TD]
[TD]X[/TD]
[TD]7/1/2019 20:56[/TD]
[TD]7/1/2019 21:04[/TD]
[TD]Success[/TD]
[TD]UNK[/TD]
[TD]final[/TD]
[/TR]
[TR]
[TD]GATN[/TD]
[TD]UNK[/TD]
[TD]RCM-2[/TD]
[TD]288[/TD]
[TD]S[/TD]
[TD]7/1/2019 21:37[/TD]
[TD]7/1/2019 21:47[/TD]
[TD]Waiting for Report[/TD]
[TD]UNK[/TD]
[TD]final[/TD]
[/TR]
[TR]
[TD]PASS[/TD]
[TD]UNK[/TD]
[TD]Radarsat-2[/TD]
[TD]60273[/TD]
[TD]X[/TD]
[TD]7/1/2019 22:27[/TD]
[TD]7/1/2019 22:38[/TD]
[TD]Success[/TD]
[TD]UNK[/TD]
[TD]final[/TD]
[/TR]
[TR]
[TD]ICAN[/TD]
[TD]UNK[/TD]
[TD]Radarsat-2[/TD]
[TD]60273[/TD]
[TD]X[/TD]
[TD]7/1/2019 22:38[/TD]
[TD]7/1/2019 22:41[/TD]
[TD]Success[/TD]
[TD]UNK[/TD]
[TD]final[/TD]
[/TR]
[TR]
[TD]PASS[/TD]
[TD]UNK[/TD]
[TD]RCM-3[/TD]
[TD]289[/TD]
[TD]S[/TD]
[TD]7/1/2019 23:15[/TD]
[TD]7/1/2019 23:25[/TD]
[TD]Waiting for Report[/TD]
[TD]UNK[/TD]
[TD]final[/TD]
[/TR]
[TR]
[TD]PASS[/TD]
[TD]UNK[/TD]
[TD]Radarsat-2[/TD]
[TD]60274[/TD]
[TD]X[/TD]
[TD]7/2/2019 0:05[/TD]
[TD]7/2/2019 0:16[/TD]
[TD]Success[/TD]
[TD]UNK[/TD]
[TD]final[/TD]
[/TR]
[TR]
[TD]ICAN[/TD]
[TD]UNK[/TD]
[TD]Radarsat-2[/TD]
[TD]60274[/TD]
[TD]X[/TD]
[TD]7/2/2019 0:19[/TD]
[TD]7/2/2019 0:22[/TD]
[TD]Success[/TD]
[TD]UNK[/TD]
[TD]final[/TD]
[/TR]
</tbody>[/TABLE]


After
[TABLE="width: 988"]
<tbody>[TR]
[TD="class: xl66, width: 72"]GATN[/TD]
[TD="class: xl66, width: 90"]UNK[/TD]
[TD="class: xl66, width: 90"]Radarsat-2[/TD]
[TD="class: xl66, width: 54"]60272[/TD]
[TD="class: xl66, width: 45"]X[/TD]
[TD="class: xl67, width: 146"]7/1/2019 20:45[/TD]
[TD="class: xl67, width: 146"]7/1/2019 20:53[/TD]
[TD="class: xl66, width: 174"]Success[/TD]
[TD="class: xl66, width: 90"]UNK[/TD]
[TD="class: xl66, width: 81"]final[/TD]
[/TR]
</tbody>[/TABLE]
Here is the exact code I ran, the times are in columns F and G:

Sub keep_2019_2100_2200()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For MY_ROWS = Range("F" & Rows.Count).End(xlUp).Row To 2 Step -1
If Year(Range("F" & MY_ROWS).Value) <> 2019 Or Hour(Range("F" & MY_ROWS)) <> 21 Or _
Year(Range("G" & MY_ROWS).Value) <> 2019 Or Hour(Range("G" & MY_ROWS)) <> 22 Then
Rows(MY_ROWS).Delete
End If
Next MY_ROWS
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub








onlyadrafter said:
Hello,

doesn't my original code not do that?

If i start with

01/04/2019 20:00 01/04/2019 21:00
01/04/2019 21:00 01/04/2019 22:00
01/04/2019 21:01 01/04/2019 22:01
01/04/2019 21:02 01/04/2019 22:02
01/04/2019 21:03 01/04/2019 22:03
01/04/2019 21:04 01/04/2019 22:04
01/04/2019 21:05 01/04/2019 22:05
01/04/2019 21:06 01/04/2019 22:06
01/04/2019 21:07 01/04/2019 22:07
01/04/2019 21:08 01/04/2019 22:08
01/04/2019 22:00 01/04/2019 23:00

then run the code, I end up with

01/04/2019 21:00 01/04/2019 22:00
01/04/2019 21:01 01/04/2019 22:01
01/04/2019 21:02 01/04/2019 22:02
01/04/2019 21:03 01/04/2019 22:03
01/04/2019 21:04 01/04/2019 22:04
01/04/2019 21:05 01/04/2019 22:05
01/04/2019 21:06 01/04/2019 22:06
01/04/2019 21:07 01/04/2019 22:07
01/04/2019 21:08 01/04/2019 22:08

perhaps you can post a sample of data, with a before and after.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
TIME LIMITATIONS
Replies
1
Views
87
cooleomter
C
A
  • Solved
Count entry by time
Replies
3
Views
253
admvlad
A
A
  • Solved
Count by time block in Date/Time Group column
Replies
5
Views
88
admvlad
A
D
  • Question Question
Can time frames/windows be used as a condition for any functions?
Replies
4
Views
124
dallas2699
D
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
406
dshtesy
D

Forum statistics

Threads
1,224,748
Messages
6,180,721
Members
452,995
Latest member
isldboy

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