Finding day of week with data above 0

codyhost

New Member
Joined
Mar 4, 2009
Messages
23
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?

Thanks in advance.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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......
 
Upvote 0
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...
 
Upvote 0
Barry,

I will try your solution.

Thanks for the prompt, detailed and informative response.


E18cincy,

I need a filtered solution.
 
Upvote 0
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.

Thanks again in advance.
 
Upvote 0
Barry,

Once again, that got us our results.

You are outstanding with your responses and quick responses.
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

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