Picking every monday from a list of dates

A

Arne Hegefors

I have a long list with interest rates and dates. The list is for every day
i.e. I have one interest rate for every day. Now I want to pick out the
interest rate for one day per week, say evey monday. Is this possible? if so
how? Is it also possible to pick out the interest rates for just the business
days (i.e. do not show sundays/saturdays)? Any help is very much appreciated!
Thanks alot!
 
A

Arne Hegefors

Yes thanks but there seems to be some trouble. In cell A1 I have: 2006-01-01.
In another cell I write: =WEEKDAY(A1;2). the return value I get is:
1900-01-05 00:00.
(I have the Swedish Excel version ). It does not seem to give me what I
want..please help me out!

"tony h" skrev:
 
M

MartinW

Hi Arne,

One way is to add a helper column and put the numbers 1 to 7 in
the first seven rows, then select all seven cells click on the fill
handle and drag it to the end of your data.

Then you can sort on that column selecting all the 1's, all the 2's etc.

HTH
Martin
 
M

MartinW

OOPS!!

Then you can sort on that column selecting all the 1's, all the 2's etc.

That should read filter not sort!!

Regards
Martin
 
B

Bob Phillips

Try this

select cells G1:G52
in the formula bar add

=IF(ISERROR(SMALL(IF(WEEKDAY(A1:A366)=2,ROW($A1:$A366),""),ROW($A1:$A20))),"
",
INDEX($A$1:$A$366,SMALL(IF(WEEKDAY(A1:A36)=2,ROW($A1:$A366),""),ROW($A1:$A52
))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

then in H1, add =IF(G1="","",VLOOKUP(G1,A:B,2,FALSE)) and copy down.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
F

Fingerjob

Hi,

Try to formate the cell that give you "1900-01-05 00:00" to General.
That should work for you.

Arne Hegefors skrev:
 
Top