# Finding day of week with data above 0

#### codyhost

##### New Member
I am a basic user of Excel.

I have an Excel spreadsheet with data from the last 10 years.

In one column it has date in dddd,mmmm,dd,yyyy format and the second column with data for the dates.

I need to have Excel extract only Tuesdays with data that is a positive number (above 0).

Is there a formula, or other method of finding only these results from the whole speadsheet?

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

#### barry houdini

##### MrExcel MVP
You could use "Advanced filter" for this.

Assuming you have dates in column A (and that they are "true dates" not text) and numbers in column B, with headers for both then you can insert 3 rows above to make the sheet look like this:
course_fees_ee.xls
ABCD
1datedataweekday
2>0FALSE
3
4datedata
5Thursday,January,01,20091
6Friday,January,02,20090
7Saturday,January,03,20090
8Sunday,January,04,20099
9Monday,January,05,20096
10Tuesday,January,06,20099
11Wednesday,January,07,20098
12Thursday,January,08,20090
13Friday,January,09,20090
14Saturday,January,10,20096
15Sunday,January,11,20090
16Monday,January,12,20090
17Tuesday,January,13,20090
Sheet3

A2 is empty

in B2 you type

=">0"

it will display as just >0

In C2 you need the formula

=WEEKDAY(A5)=3

Now select the whole range of data starting at A4, e.g. A4:B4000. [You can do that easily by just typing the range into the box above cell A1 and pressing ENTER]

Now use

Data > Filter > Advanced filter

In the dialog box set the list range, that should default to the selected range, i.e. \$A\$4:\$B\$4000, and the criteria range should be \$A\$1:\$C\$2

Select "Filter the list in place" and press OK

You should now just see a list of the records you want......

#### e18cincy

##### New Member
When you say extract, do you mean you're going to copy the data and post is elsewhere? Or do you just need a filtered solution, which I believe has already been posted.

Thanks...

#### codyhost

##### New Member
Barry,

Thanks for the prompt, detailed and informative response.

E18cincy,

I need a filtered solution.

#### codyhost

##### New Member
Barry,

That worked perfectly.

Is there a way to do it for the First Tuesday of each Month?

Sorry to be so demanding but these results ask the question.

#### barry houdini

##### MrExcel MVP
Just change the C2 formula to the following and filter again

=AND(WEEKDAY(A5)=3,DAY(A5)<8)

#### codyhost

##### New Member
Barry,

Once again, that got us our results.

You are outstanding with your responses and quick responses.

#### codyhost

##### New Member
Barry,

What part of the formula do I change to get the second Tuesday of each month? Ideally the same for the third, forth and possibly 5th Tuesday of each month.

I tried modifying the <8 but could not find a value that gets just second tuesdays.

#### codyhost

##### New Member
Barry,

Is there a manual for these references in the formula's?

Mark

#### barry houdini

##### MrExcel MVP
Hello Mark, there's no manual I know of but for first Tuesday it's < 8 because the first Tuesday must be within the first 7 days of the month. Using the same logic for 2nd Tuesday then DAY must be >=8 and <=14, so

=AND(WEEKDAY(A5)=3,DAY(A5)>=8,DAY(A5)<=14)

for third you can replace 8 and 14 with 15 and 21 then 22 and 28 for fourth. Of course there won't always be a 5th Tuesday but for those there are you can use

=AND(WEEKDAY(A5)=3,DAY(A5)>28)

Replies
2
Views
568
Replies
1
Views
164
Replies
15
Views
1K
Replies
7
Views
431
Replies
8
Views
647

1,190,700
Messages
5,982,362
Members
439,776
Latest member
mathewduffy

### 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.

### Which adblocker are you using?

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

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