Advanced Filter problem

Michaels

Active Member
Joined
Apr 2, 2009
Messages
404
Hello,

My requirement is to extract Wed, Thu, Sat and Sun (or any other days as required) based on start date and end date. So for example, between the start date of 01/01/2020 and end date of 25/01/2020, I want the dates of Sat, Sun and Mon. That would be dates in one column and days in next column.

I have tried Auto Filter, Advanced Filter and even some formulas. Also I tried VBA but to no avail.

I would appreciate any help to solve this using formula or VBA.

Thank you for your help.

Regards
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Are you able to use Power Query (Get&Transform)?
Update your profile about excel version
Is that what you want?
ParameterValueDateDay Name
StartDate01/01/202001/01/2020Wednesday
EndDate25/01/202002/01/2020Thursday
04/01/2020Saturday
05/01/2020Sunday
08/01/2020Wednesday
09/01/2020Thursday
11/01/2020Saturday
12/01/2020Sunday
15/01/2020Wednesday
16/01/2020Thursday
18/01/2020Saturday
19/01/2020Sunday
22/01/2020Wednesday
23/01/2020Thursday
25/01/2020Saturday
 
Upvote 0
Hi Sandy,

Thanks for your reply. I am using Excel 2007. Yes this is what I am looking for with formula or VBA, without power query.

Thanks for your help.
 
Upvote 0
Ops, I just noticed you are using XL2007 so ignore post above
but try to update your profile about Excel version
 
Upvote 0
You can never have too many formulas. Now remember I am using United States date format. See if this works. I just noticed that it doesn't show the table. If you insert a table then you can filter using the numbers representing the day. Also I custom format column A with ddd


20-09-09 Date.xlsx
ABCDEFG
1Monday = 1Tuesday = 2Wednesday = 3Thursday = 4Friday = 5Saturday = 6Sunday = 7
2DateDay of the Week
3 Wed 1/1/20203
4 Thu 1/2/20204
5 Fri 1/3/20205
6 Sat 1/4/20206
7 Sun 1/5/20207
8 Mon 1/6/20201
9 Tue 1/7/20202
10 Wed 1/8/20203
11 Thu 1/9/20204
12 Fri 1/10/20205
13 Sat 1/11/20206
14 Sun 1/12/20207
15 Mon 1/13/20201
16 Tue 1/14/20202
17 Wed 1/15/20203
18 Thu 1/16/20204
19 Fri 1/17/20205
20 Sat 1/18/20206
21 Sun 1/19/20207
22 Mon 1/20/20201
23 Tue 1/21/20202
24 Wed 1/22/20203
25 Thu 1/23/20204
26 Fri 1/24/20205
27 Sat 1/25/20206
Sheet1 (3)
Cell Formulas
RangeFormula
B3:B27B3=WEEKDAY(A3,2)
 
Upvote 0
try =CHOOSE(MATCH(WEEKDAY(A2,1),{1,2,3,4,5,6,7},0),"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday")
change 1 to 2 (US style) or to default of your country style date
 
Upvote 0
Thanks Ezyguy. I have my criteria this way:

Start Date..................End Date.....................Days
01/01/2020............25/01/2020..................Wed
.............................................................................Thu
..............................................................................Fri

So A1: Start Date
A2: the date
B1: End Date
B2: the date
C1: Days
C2: Wed
C3: Thu
C4: Fri

The end result that I am looking for would look like this:

DatesDays
01/01/2020​
Wed
02/01/2020​
Thu
03/01/2020​
Fri
08/01/2020​
Wed
09/01/2020​
Thu
10/01/2020​
Fri
15/01/2020​
Wed
16/01/2020​
Thu
17/01/2020​
Fri
22/01/2020​
Wed
23/01/2020​
Thu
24/01/2020​
Fri
 
Upvote 0
maybe
Book4
HI
4DateDay
501/01/2020Wednesday
602/01/2020Thursday
703/01/2020Friday
1107/01/2020Tuesday
1208/01/2020Wednesday
1309/01/2020Thursday
1410/01/2020Friday
1814/01/2020Tuesday
1915/01/2020Wednesday
2016/01/2020Thursday
2117/01/2020Friday
2521/01/2020Tuesday
2622/01/2020Wednesday
2723/01/2020Thursday
2824/01/2020Friday
3228/01/2020Tuesday
3329/01/2020Wednesday
3430/01/2020Thursday
Sheet3
Cell Formulas
RangeFormula
I32:I34,I25:I28,I18:I21,I11:I14,I5:I7I5=CHOOSE(MATCH(WEEKDAY(H5,2),{1,2,3,4,5,6,7},0),"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday")

filtered by required days
you'll need to find way with StartDate and EndDate
 
Last edited:
Upvote 0
Michaels here is a filtered 3 and 6 or Wed and Sat. I think the key is Weekday function. You just have to adapt it to your worksheet.

20-09-09 Date.xlsx
ABCDEFG
1Monday = 1Tuesday = 2Wednesday = 3Thursday = 4Friday = 5Saturday = 6Sunday = 7
2DateDay of the Week
3 Wed 1/1/20203
6 Sat 1/4/20206
10 Wed 1/8/20203
13 Sat 1/11/20206
17 Wed 1/15/20203
20 Sat 1/18/20206
24 Wed 1/22/20203
27 Sat 1/25/20206
Sheet1 (3)
Cell Formulas
RangeFormula
B3,B27,B24,B20,B17,B13,B10,B6B3=WEEKDAY(A3,2)
 
Upvote 0
Hello mr. Michael
Is nice to see how sandy666 and Ezguy4u want to help you.
Now I want to. But I have a problem trying to understand what do you really are looking for
I tried so hard and break apart your require like this, so check:

This is exactly your speech….

extract Wed, Thu, Sat and Sun (or any other days as required) based on start date and end date. So for example, between the start date of 01/01/2020 and end date of 25/01/2020, I want the dates of Sat, Sun and Mon. That would be dates in one column and days in next column.

Between A and B [A= January 1-20, B January 25/20]
Beginning of your statement[extract => meaning => delete] No wed, thu, sat, sun
But at the end you say --- I want the dates of sat, sun and mon.
But after in your criteria way you listed only wed thu and fri.
I am lost.



Or any other days as required = input day x (as require = you don’t know, or, surprise!,)
My question is: each day in YOUR REQUIREMENT have a different price or value or number [surprise or…]
So if no surprices occure, then is just
According to your criteria way
Work 3 days weekly.

you just have a simple multiplication problem.
that's what looks like for me,
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

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