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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,106
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
 

Michaels

Active Member
Joined
Apr 2, 2009
Messages
404
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.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,106
Ops, I just noticed you are using XL2007 so ignore post above
but try to update your profile about Excel version
 

Ezguy4u

Board Regular
Joined
Feb 10, 2010
Messages
125
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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)
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,106
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
 

Michaels

Active Member
Joined
Apr 2, 2009
Messages
404

ADVERTISEMENT

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
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,106
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:

Ezguy4u

Board Regular
Joined
Feb 10, 2010
Messages
125
Office Version
  1. 2019
Platform
  1. Windows
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)
 

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
612
Office Version
  1. 2010
Platform
  1. Windows
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,
 

Watch MrExcel Video

Forum statistics

Threads
1,114,314
Messages
5,547,170
Members
410,775
Latest member
alal1030
Top