list of just weekend dates

Let A2 contain the start date, and B2 contain the end date, then try the following...

C2:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)>5))

D2, copied down:

=IF(ROWS(D$2:D2)<=$C$2,SMALL(IF(WEEKDAY(ROW(INDIRECT($A$2&":"&$B$2)),2)>5,ROW(INDIRECT($A$2&":"&$B$2))),ROWS(D$2:D2)),"")

...confirmed with CONTROL+SHIFT+ENTER..

Hope this helps!

jlhurrell

Firstly sorry to but in on your post.....

Domenic

Trying to brsh up on my formulas as my VB is okay but formulas......

would you mind explaining how the formula achieves its result?

Thanks

Alternatively :-

Put the date of the first Sat in A1
Put the date of the next day (Sun) in A2
In A3 put =A1+7
In A4 put =A2 +7
Select A3:A4 and drag down as far as required

Domenic

Trying to brsh up on my formulas as my VB is okay but formulas......

would you mind explaining how the formula achieves its result?

Thanks

Click to expand...

Assuming that A2 contains January 1, 2007, and B2 contains January 31, 2007, here's how this part of the formula is evaluated...

WEEKDAY(ROW(INDIRECT($A$2&":"&$B$2)),2)

WEEKDAY(ROW(INDIRECT(37621&":"&37651)),2)

WEEKDAY({37621;37622;,37623; ... 37651},2)

{1;2;3; ... 3}

This is what's happening above. As you know, dates are stored as serial numbers, and the serial numbers for January 1st and January 31st are 37621 and 37651, respectively. So $A$2&":"&$B$2 returns the text value 37621:37651. INDIRECT then returns a reference from the specified text, which is passed to the ROW function. The ROW function returns an array of row numbers from the reference returned by INDIRECT. These row numbers also represent serial numbers or dates. In turn, this array is passed to the WEEKDAY function and returns the weekday number for each row/serial number. As far as the IF part of the formula...

IF(WEEKDAY(ROW(INDIRECT($A$2&":"&$B$2)),2)>5,ROW(INDIRECT($A$2&":"&$B$2)))

...if the weekday number for the array of row/serial numbers is greater than 5 (6 and 7 representing Saturday and Sunday), the corresponding row/serial number is returned, otherwise FALSE is returned. This array is passed to the SMALL function and returns the smallest number in the array, the smallest number being determined by ROWS(D$2:D2), which returns 1. Remember that the number returned is a row number, which actually represents a serial number/date.

Hope this helps!

Domenic

Thanks that helps alot as i have MAJOR trouble with dates in excel, we simply dont get along!

cheers guys.

ADAMC

Do you need an explanation of my suggestion?

Boller, after Domenics explanation i think i can just about get to grips with your suggestion!