----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
Last edited by tomdenners; 06-24-2011 at 07:55 AM.
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))
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]
Audere est facere
Many thanks Bob!
@daddylonglegs - It would be really useful if you could walk me through your alternative.
Last edited by tomdenners; 06-24-2011 at 08:23 AM.
I explained it (after a fashion) here
Audere est facere
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks