Sorting spreadsheet by time

[systemdude]

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.

 

[onlyadrafter]

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.

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

 

[systemdude]

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.

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]

 

[onlyadrafter]

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.

 

[systemdude]

Before:

GATN	UNK	Radarsat-2	60272	X	7/1/2019 20:45	7/1/2019 20:53	Success	UNK	final
ICAN	UNK	Radarsat-2	60272	X	7/1/2019 20:56	7/1/2019 21:04	Success	UNK	final
GATN	UNK	RCM-2	288	S	7/1/2019 21:37	7/1/2019 21:47	Waiting for Report	UNK	final
PASS	UNK	Radarsat-2	60273	X	7/1/2019 22:27	7/1/2019 22:38	Success	UNK	final
ICAN	UNK	Radarsat-2	60273	X	7/1/2019 22:38	7/1/2019 22:41	Success	UNK	final
PASS	UNK	RCM-3	289	S	7/1/2019 23:15	7/1/2019 23:25	Waiting for Report	UNK	final
PASS	UNK	Radarsat-2	60274	X	7/2/2019 0:05	7/2/2019 0:16	Success	UNK	final
ICAN	UNK	Radarsat-2	60274	X	7/2/2019 0:19	7/2/2019 0:22	Success	UNK	final

<colgroup><col><col span="2"><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>

After

GATN

UNK

Radarsat-2

60272

X

7/1/2019 20:45

7/1/2019 20:53

Success

UNK

final

<tbody>
</tbody>

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

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.