How many Tuesdays in a range of dates?

**Lou Sander** · 2012-02-14 15:46

I have a highly-evolved workbook used for keeping track of many aspects of a 100-person Powerball and Mega Millions lottery pool that I run for friends and acquaintances. Many of that workbook's best features have come from answers to questions posted here, so thanks to all for their help so far. Now I need something else.

The lottery pools run from date X to date Y, usually about 13 weeks apart. During the period between those dates, there are lottery drawings every Tuesday, Wednesday, Friday, and Saturday. I need a way to tell how many drawings, total, take place between date X and date Y.

The idea is there will be three cells in a row on a worksheet. Cell #1 will contain the start date (date X), cell #2 will contain the end date (date Y), and cell #3 will contain the total number of drawings during the pool. My problem is how to get cell #3 to do what I want it to.

It looks to me like some sort of user-defined function might be useful here, and might even already exist. (I've dabbled in these, but it's been a LONG time since I last touched them.) Arguments of the function could be start date and end date. The function could return seven values (can functions do that?): Number of Sundays between start date and end date, number of Mondays, number of Tuesdays, etc.

Any sort of guidance, solution, etc. will be greatly appreciated. (And if we ever win a Jackpot, we will certainly make a BIG donation to this forum).

**RetiredGeek** · 2012-02-14 17:08

Lou,

Here's a UDF that will do the trick.

Code:

Option Explicit

Public Function LotteryDays() As Integer

  Dim iCntOfdays     As Integer
  Dim iDayOfWeek     As Integer
  Dim iCntr          As Integer
  Dim dBaseDate      As Date
  
  
  iCntOfdays = ([B1] - [A1]) + 1
  dBaseDate = [A1]
  
  For iCntr = 1 To iCntOfdays
     iDayOfWeek = WorksheetFunction.Weekday(dBaseDate + iCntr)
     Select Case iDayOfWeek
        Case 3, 4, 6, 7
          LotteryDays = LotteryDays + 1
     End Select
  Next iCntr
  
End Function

Attached is an .xlsm file that has the data I used to test the function.

I'm sure someone will come up with a formula that fits in a cell but if not you have this as a fallback.

**sdckapr** · 2012-02-14 17:53

If the start date is in A1, the enddate is in A2 you can use the formula:
=INT(($A$2-($A$1+3-WEEKDAY($A$1)+7*(3<WEEKDAY($A$1))))/7)+1

to calculate the number of Tuesday (weekday = 3). which answers the question posed in the title. (Of course you can enter the dates in the formula instead of the cell references if desired.

To get the sums of the Tues, Wed, Fri, and Sat in the range (Weekdays = 3,4,6,7) you can use the array formula (confirm with ctrl-shift-enter):
=SUM(INT(($A$2-($A$1+WEEKDAY({3,4,6,7})-WEEKDAY($A$1)+7*(WEEKDAY({3,4,6,7})<WEEKDAY($A$1)) ))/7)+1)

Steve

**RetiredGeek** · 2012-02-14 18:35

Steve,

See I told you someone {I knew it would be you but didn't want to put any pressure on} would do it. I tried it out on my test sheet but got an error then I realized that you interpreted "in a row" to be down a column where I interpreted it to be across a row. Simply changing the $A$2 to $B$1 fixed it nicely. BRAVO Steve!

**RetiredGeek** · 2012-02-15 06:06

Hi Again All,

Here's a New and Improved UDF allowing the caller to specify the cells holding the beginning & ending dates and a string containing the list of days-of-week to be counted. If the days-of-week string is ommited it will default to 1 or Sundays.

Calling Example for thread example: =LotteryDays(A1,B1,"3467")

Code:

Option Explicit

Public Function LotteryDays(dteStart As Date, dteEnd As Date, Optional zDOWs As String) As Integer

  Dim iCntOfdays     As Integer
  Dim iDayOfWeek     As Integer
  Dim iCntr          As Integer
  
  If IsMissing(zDOWs) Then zDOWs = "1"
  iCntOfdays = (dteEnd - dteStart) + 1
  
  For iCntr = 1 To iCntOfdays
     iDayOfWeek = WorksheetFunction.Weekday(dteStart + iCntr)
     Select Case InStr(zDOWs, Format(iDayOfWeek, "#"))
        Case Is > 0
          LotteryDays = LotteryDays + 1
     End Select
  Next iCntr
  
End Function

**Lou Sander** · 2012-02-15 20:03

This is great! I will play with it tomorrow. In the meantime, I have to figure out how to get this forum to default to sending me email whenever there is a reply posted. I've sat here since my original post, waiting for that email. Sheesh!

I'm thinking that in the provided example that changing

If IsMissing(zDOWs) Then zDOWs = "1"

to

If IsMissing(zDOWs) Then zDOWs = "1234567"

the function would just return the number of days between dteStart and dteEnd. Am I right?

**MartinM** · 2012-02-16 05:12

The SPAM above has been reported to the Forum moderators and has been removed

**RetiredGeek** · 2012-02-16 06:03

Originally Posted by Lou Sander

This is great! I will play with it tomorrow. In the meantime, I have to figure out how to get this forum to default to sending me email whenever there is a reply posted. I've sat here since my original post, waiting for that email. Sheesh!

I'm thinking that in the provided example that changing

If IsMissing(zDOWs) Then zDOWs = "1"

to

If IsMissing(zDOWs) Then zDOWs = "1234567"

the function would just return the number of days between dteStart and dteEnd. Am I right?

Lou,

Yes, you are correct.

**zeddy** · 2012-02-16 07:37

Hi Steve

A lovely formula!

Re: To get the sums of the Tues, Wed, Fri, and Sat in the range (Weekdays = 3,4,6,7) you can use the array formula (confirm with ctrl-shift-enter):
=SUM(INT(($A$2-($A$1+WEEKDAY({3,4,6,7})-WEEKDAY($A$1)+7*(WEEKDAY({3,4,6,7})<WEEKDAY($A$1)) ))/7)+1)

I notice that when arrays such as {3,4,6,7} are used directly in formulas, you don't need to use ctrl-shift-enter, but if you refer to a cell range containing the elements, then you must use ctrl-shift-enter.
So your formula will work either way.
I suppose it is always safer to use ctrl-shift-enter, but do you know if there are any advantages to NOT using??

zeddy

**sdckapr** · 2012-02-16 08:07

Thanks, I hadn't noticed the issue with directly entered arrays vs indirect arrays with ranges (I don't use them that much). I suppose that the ctrl-shift-enter is to trigger/translate that the range is an array. Some "array formulas" (like sumproduct) implicitly presume they are arrays and thus they don't need the ctrl-shift-enter either...

I can't think of any advantage to use it or not use it, though I suppose there could be a little more "overhead" as an explicit array. I would worry more about the general use of the array formulas and the number of calculations they may represent that may be recalculated with every entry.

Steve

**zeddy** · 2012-02-16 11:32

Hi Steve

I think the number of calcs would be the same either way.
When I want to check complicated formulas I often use the [F9] trick in the formula bar, for example, in your formula, if I highlight just the section WEEKDAY({3,4,6,7})-WEEKDAY($A$1) within the formula bar, and then press [F9], it shows me the results {0,1,3,4} for this part of the calc.

I guess I would continue to play safe and use Ctrl-Shift-Enter whenever any array ranges or direct array elements are used in formulas.

I always find your thoughts very useful.
Many thanks

zeddy

**sdckapr** · 2012-02-16 11:47

I agree, there is no difference with or without the ctrl-shift-enter. All that does is explicitly tell excel that the formula is an array formula. Some formulas it can tell implicitly. My point about the "general use" was more an aside where people try to use them as a substitute/mimic for something like a pivot table and in those cases it can use much overhead

Steve

**Lou Sander** · 2012-03-11 21:56

Sorry it has taken me so long to implement this and report back. Believe it or not, sometimes things come up that are (or seem to be) more important than spreadsheet development. Sigh!!!

I've used Retired Geek's improved UDF. (The other stuff was too complicated with the CTRL+Enter business.)

There's a flaw, probably easily corrected, but I don't see how to do it myself:

When one appies the UDF to various date ranges, one discovers that Sunday is day 2, not day 1; Monday is day 3, not day 2, etc.

In other words, the function works, but the numbers used for the days are off by one from what would be expected. Try it on April, 2012, and see what I mean.

**zeddy** · 2012-03-12 04:49

Hi Lou

First of all, can you please give us a list of all the things that are more important than spreadsheet development.

Secondly, what day do you want us to try it on? You said "Try it on April, 2012".

For the weekday function, Sunday should be 1, Monday 2 etc.

zeddy

**zeddy** · 2012-03-12 05:06

Hi Lou

Try this amended code:

Code:

Public Function LotteryDays(dteStart As Date, dteEnd As Date, Optional zDOWs As String) As Integer
  Dim iCntOfdays     As Integer
  Dim iDayOfWeek     As Integer
  Dim iCntr          As Integer
  
  If IsMissing(zDOWs) Then zDOWs = "1"
  iCntOfdays = (dteEnd - dteStart)
  
  For iCntr = 0 To iCntOfdays
     iDayOfWeek = WorksheetFunction.Weekday(dteStart + iCntr)
     Select Case InStr(zDOWs, Format(iDayOfWeek, "#"))
        Case Is > 0
          LotteryDays = LotteryDays + 1
     End Select
  Next iCntr
  
End Function

I believe the previous code would 'miss' the start date, because it 'added 1' to it in the For loop.

zeddy

Thread: How many Tuesdays in a range of dates?

Thread Tools