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
