I was only saying that it would be easier for people to remember 1=Sunday, 2=Monday, 3=Tuesday etc, and that one can rely on the default return_type (which is 1) without actually having to pass it in the function. Makes the formula look more comprehensible. :)
Also, in your example below, =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A16&":"&A17)),1)<2)) will give count of all Sundays and =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A16&":"&A17)),1)>6)) will give count of all Saturdays, which kind of makes it difficult to correlate to, and more so with the greater to and less than conditionals used. So basically, it is easier to remember 1,2,3,4,5,6,7 corresponds to Sunday, Monday, Tuesday etc.... Having said that, this is just my opinion, and I am open to standing corrected if there is any proven alter-argument. Regards, Sam On Mon, Oct 10, 2011 at 11:55 PM, NOORAIN ANSARI <noorain.ans...@gmail.com>wrote: > Dear SAM, > > I am agree with your statement.. > and thanks for your valuable advice.. > > But if we use formula with little bit correction then we can find monday, > tuesday count......... > it works similar to your formula... > For Monday > =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A16&":"&A17)),1)<2)) > > For Tuesday > > =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(C3&":"&D3)),1)>6)) > > -- > Thanks & regards, > Noorain Ansari > *http://excelmacroworld.blogspot.com/*<http://excelmacroworld.blogspot.com/> > *http://noorain-ansari.blogspot.com/*<http://noorain-ansari.blogspot.com/> > > On Mon, Oct 10, 2011 at 11:39 PM, Sam Mathai Chacko <samde...@gmail.com>wrote: > >> Hey Noorain, what's up :) >> >> Similar methods used, but, just to touch-base on the differences, what I >> made use of is the fact that the default return_type of the WEEKDAY function >> is 1, and it also means that the first day is expected as a Sunday. So in >> effect, if you equate the resultant array to any number from 1 to 7, it will >> be equivalent to Sunday to Saturday respectively. So all you need to do to >> find the number of Mondays for example from my formula, is to change 1 to 2. >> >> In your formula, if you wanted to find the number of Mondays, you'd have >> to modify the conditional also, from >6(OR =7 cause it can never be greater >> than 7) to <2(OR =1 cause it can never be less than 1) >> >> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A16&":"&A17)),2)<2)) >> >> OR >> >> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A16&":"&A17)),2)=1)) >> >> So I would recommend going with >> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))=2)) for Monday >> >> Regards, >> >> Sam >> >> >> On Mon, Oct 10, 2011 at 11:17 PM, Sam Mathai Chacko >> <samde...@gmail.com>wrote: >> >>> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))=1)) >>> >>> Where A1 and B1 are your start dates, and 1=Sunday, 2=Monday, 3=Tuesday >>> etc >>> >>> Regards, >>> >>> Sam Mathai Chacko (GL) >>> >>> On Mon, Oct 10, 2011 at 10:57 PM, RockyFontane <vtorral...@gmail.com>wrote: >>> >>>> I am trying to count the number of Sunday's between two date ranges. >>>> For example, Start Date: 9/25/2011; End Date: 10/24/2011. The >>>> number of Sunday's between these two dates are 5. What's the best way >>>> to write that in a formula? >>>> >>>> Thanks for your help! >>>> >>>> -- >>>> >>>> ---------------------------------------------------------------------------------- >>>> Some important links for excel users: >>>> 1. Follow us on TWITTER for tips tricks and links : >>>> http://twitter.com/exceldailytip >>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com >>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >>>> >>>> To post to this group, send email to excel-macros@googlegroups.com >>>> >>>> <><><><><><><><><><><><><><><><><><><><><><> >>>> Like our page on facebook , Just follow below link >>>> http://www.facebook.com/discussexcel >>>> >>> >>> >>> >>> -- >>> Sam Mathai Chacko >>> >> >> >> >> -- >> Sam Mathai Chacko >> >> -- >> >> ---------------------------------------------------------------------------------- >> Some important links for excel users: >> 1. Follow us on TWITTER for tips tricks and links : >> http://twitter.com/exceldailytip >> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >> 3. Excel tutorials at http://www.excel-macros.blogspot.com >> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >> >> To post to this group, send email to excel-macros@googlegroups.com >> >> <><><><><><><><><><><><><><><><><><><><><><> >> Like our page on facebook , Just follow below link >> http://www.facebook.com/discussexcel >> > > > > > -- > > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/discussexcel > -- Sam Mathai Chacko -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
