How to filter a list of dates so only rows where the date is a specific day

[IrishDMan]

Hi all,

I've a worksheet with two columns, one with numbers, and the other with repeating dates in a custom format of dd/mm/yyyy for a month when the numbers were recorded, the number of rows will depend on how busy it was so the number of rows will change per month.

e.g.

100	22/06/2020 12:45
50	22/06/2020 12:46
250	22/06/2020 13:50
80	23/06/2020 09:52
12	23/06/2020 09:52
500	24/06/2020 10:11
785	24/06/2020 10:11

I'm looking to see if I can filter the worksheet, so that only rows where the date matches a specific day as typed into a cell

e.g If I type in Monday into the cell the list is filtered to only show the dates and numbers that were recorded on a Monday

950	15/06/2020 09:18	Day Filter
55	15/06/2020 15:01	Monday
9	15/06/2020 17:55
100	22/06/2020 12:45
50	22/06/2020 12:46
250	22/06/2020 13:50

I've looked at different functions such as workday(), Text() etc and I can filter if I add columns to the worksheet, e.g. C1=TEXT(B1,"dddd") and filter based on this but I'd like to try and not have to add the additional columns and have to filter based on this, and if possible filter the rows just on a day that I enter into a specific cell.

Thanks in advance

 

[pjmorris]

would conditional formating work? Strictly its not what you asked for as it doesnt filter, but it would highlight each row that matched the filter? IF so, then use a formula to select the cells to format. Use this formula assuming the date column starts at B1 and the filter is in C2.

=WEEKDAY($B1)=MATCH($C$2,$D$1:$D$7)

in cells D1:D7 enter: Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday

HTH

 

[vcoolio]

Hello IrishDMan,

You could use VBA to do this:-

Assume:-
- Your data is in Columns A:B.
- Row1 is headings.
- Data starts in Row2.
- The "day filter" is in cell E1.
- CellE1 has a data validation drop down with the days of the week.
- Column Z is a temporary helper column (and out of sight) in which the TEXT function is used to find the day name.

Private Sub Worksheet_Change(ByVal Target As Range)

        Dim lr As Long: lr = Range("A" & Rows.Count).End(xlUp).Row

If Intersect(Target, Range("E1")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub

        Range("Z2:Z" & lr) = "= TEXT(B2,""DDDD"")"
        Range("Z1:Z" & lr).AutoFilter 1, Target
        
End Sub

When you select a day from the drop down, the code will instantly filter all items related to the selection.

To implement this code:
- Right click on the sheet tab.
- Select "View Code" from the menu that appears.
- In the big white code field that then appears, paste the above code.

Another very good option to use is the inbuilt FILTER function but you will need the latest version of Office to be able to use this.

I hope that this helps.

Cheerio,
vcoolio.

 

[IrishDMan]

Hello IrishDMan,

You could use VBA to do this:-

Assume:-
- Your data is in Columns A:B.
- Row1 is headings.
- Data starts in Row2.
- The "day filter" is in cell E1.
- CellE1 has a data validation drop down with the days of the week.
- Column Z is a temporary helper column (and out of sight) in which the TEXT function is used to find the day name.

Private Sub Worksheet_Change(ByVal Target As Range)

        Dim lr As Long: lr = Range("A" & Rows.Count).End(xlUp).Row

If Intersect(Target, Range("E1")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub

        Range("Z2:Z" & lr) = "= TEXT(B2,""DDDD"")"
        Range("Z1:Z" & lr).AutoFilter 1, Target
       
End Sub

When you select a day from the drop down, the code will instantly filter all items related to the selection.

To implement this code:
- Right click on the sheet tab.
- Select "View Code" from the menu that appears.
- In the big white code field that then appears, paste the above code.

Another very good option to use is the inbuilt FILTER function but you will need the latest version of Office to be able to use this.

I hope that this helps.

Cheerio,
vcoolio.

Hi Vcoolio,

Thanks very much for this code, I will give it a try and let you know.

 

[IrishDMan]

would conditional formating work? Strictly its not what you asked for as it doesnt filter, but it would highlight each row that matched the filter? IF so, then use a formula to select the cells to format. Use this formula assuming the date column starts at B1 and the filter is in C2.

=WEEKDAY($B1)=MATCH($C$2,$D$1:$D$7)

in cells D1:D7 enter: Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday

HTH

Hi Peter,

Thanks but I'm not sure this would work, but I will try it.

I eventually want to take the filtered data into another sheet and build charts from it

 

[pjmorris]

hmmm, if you want charts from it I'd be thinking about using a Pivottable - good luck.

 

[IrishDMan]

would conditional formating work? Strictly its not what you asked for as it doesnt filter, but it would highlight each row that matched the filter? IF so, then use a formula to select the cells to format. Use this formula assuming the date column starts at B1 and the filter is in C2.

=WEEKDAY($B1)=MATCH($C$2,$D$1:$D$7)

in cells D1:D7 enter: Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday

HTH

Hi Peter,

Thanks but I'm not sure this would work, but I will try it.

I eventually want to take the filtered data into another sheet and build charts from it

Hello IrishDMan,

You could use VBA to do this:-

Assume:-
- Your data is in Columns A:B.
- Row1 is headings.
- Data starts in Row2.
- The "day filter" is in cell E1.
- CellE1 has a data validation drop down with the days of the week.
- Column Z is a temporary helper column (and out of sight) in which the TEXT function is used to find the day name.

Private Sub Worksheet_Change(ByVal Target As Range)

        Dim lr As Long: lr = Range("A" & Rows.Count).End(xlUp).Row

If Intersect(Target, Range("E1")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub

        Range("Z2:Z" & lr) = "= TEXT(B2,""DDDD"")"
        Range("Z1:Z" & lr).AutoFilter 1, Target
       
End Sub

When you select a day from the drop down, the code will instantly filter all items related to the selection.

To implement this code:
- Right click on the sheet tab.
- Select "View Code" from the menu that appears.
- In the big white code field that then appears, paste the above code.

Another very good option to use is the inbuilt FILTER function but you will need the latest version of Office to be able to use this.

I hope that this helps.

Cheerio,
vcoolio.

Thanks Vcoolio that worked a treat, looking to see if I can extend it so I can filter between the times, but thanks for your help.

 

[Avra]

Hi *IrishDMan,

Try setting up your data in a table. Select any cell in your data and then, from the ribbon, select Insert\Table. Once you've done so, click anywhere in your table. Now, on the far right of your ribbon you should see Table Design. Select Table Design\Insert Slicer\Day. Now you can filter any day of the week and create a chart as you would like.

 

[vcoolio]

You're welcome IrishDMan, Glad to have been able to assist.

Cheerio,
vcoolio.