WEEKDAY formula confusion

[tomdenners]

----M-----------------------------N------------------------------O------------------------------------P
1--Cal Start Date-----------Cal End Date--------------"1 = Sun,2 = Mon…"--------No of specific days
2--27/07/2010----------------15/11/2010----------------2-------------------------------------16


I had this spreadsheet sent to me. In the specific days column is the array formula

{=SUM(--(WEEKDAY(M2-1+ROW(INDIRECT("1:"&TRUNC(N2-M2)+1)))=O2))}

Can someone break this down and explain it to me, because I am LOST!

Many thanks,

Tom

[Bob Phillips]

This part of the formula

M2-1+ROW(INDIRECT("1:"&TRUNC(N2-M2)+1))

builds up an array of all of the dates between the values in M2 and N2. The WEEKDAY is obtained for each of these dates, and compared against the value in O2, to give an array of TRUE/FALSE as to whether each date is that day of the week. The -- coerces those TRUE/FALSE values to 1/0 values, which SUM adds up to count the number of days in that date range that are the nominated days.

It can be written more simply as

=SUM(--(WEEKDAY(ROW(INDIRECT(N2&":"&M2)))=O2))

or if you want text day names, Sun, Mon, use

=SUM(--(TEXT(ROW(INDIRECT(N2&":"&M2)),"ddd")=O2))

[daddylonglegs]

Perhaps no easier to understand but this shorter non-array version will give you the same results

=INT((WEEKDAY(M2-O2)+N2-M2)/7)

[I can explain it if you want]

[tomdenners]

Many thanks Bob!

@daddylonglegs - It would be really useful if you could walk me through your alternative.

[daddylonglegs]

I explained it (after a fashion) here

[tomdenners]

Holy Moly, I'm going to have to get a cup of coffee before I read through that!

Thanks for your help though, really handy.