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
